User Form focus

[Glen Mettler]

I have a user form that contains options that will be place in the selected
cell. It works fine in modal status. However, I wanted to make the form
non-modal so the user could select more than one cell to insert the data so
they would not have to repeat the action several times.

Here is the problem. When I set the form non-modal, the focus remains in
the cell awaiting input. I cannot select the form. However, I can move the
cursor to another cell and then back and then select the form. I tried to
set the focus to the form or an object in the form (like the OK button).

Have I missed something? I want to run the form non-modal and have the
focus on the form when it is envoked. Is that possible?

Glen

 

[keepITcool]

Just a guess..

is the form called from a button on a sheet?
(from the controls Toolbox)..

check the button's properties..
ensure that "TakeFocusOnClick" is disabled.

or.. in the macro that calls the form,
activecell.activate
userform1.show vbModeless






keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Glen Mettler]

The form is called from a doubleclick in the cell. I have code in the
SheetBeforeDoubleClick event for the workbook. The code captures the
column. Then a series of If statements call the form if the column matches:
for example:
If sCol = Range("Dist_Modl").Value Then
pRow = Selection.Row
EACTcol = Range("EACT").Value
hrscol = Range("Dist_Modl").Value
If Cells(pRow, EACTcol).Value = "T" Or Cells(pRow, EACTcol).Value =
"L" Then
HoursDistribution.Show
End If
'Cells(ActiveCell.Row + 1, sCol).Select
End If

note that the last code statement of the major If statement
(Cells(ActiveCell.row...) is commented. If I uncomment this statment, the
form works as I want it to but it cell is now on the next row. I want the
focus to remain on the cell selected. If I comment the code, the focus
remains in the cell waiting for user input and does not go to the form.

I tried KeepITcool's code and it didn't change anything.

There should be a way to do this.

Glen

 

[Tom Ogilvy]

replace the commented line with

Cancel = True