I have noticed that after showing a user form in VBA macro and placing a
variable value from the user form in a worksheet cell, the Excel-application
is not active.

Example:
'---------------------------------------
' // Show a form to let a user select a product code
frmTuotteet.Show
' // Inside the form the procut codes are shown in a list box
frmTuotteet.lstTuotteet.SetFocus
'(here the form is active until the user has made the selection)
rs.Close ' Close the recordset
Set rs = Nothing
Set conTyomaar = Nothing
' // Check the user seelction
If Trim(Val_Tuote) < "" Then
' // Place the value in a worksheet cell
Worksheets("Työmääräin").Activate
With ActiveSheet
.Cells(ir, ic).Value = Val_Tuote ' // This is ok
End With
End If

Worksheets("Työmääräin").Activate
'---------------------------------------

After this the worksheet is not active (selected), so a user must click on
the worksheet to continue.

The problem is that I would like to execute another VBA subroutine after the
value from the user form is returned into a worksheet cell, but now this
doesn't work.

Any solutions ?


Kaj B.

After this the worksheet is not active (selected), so a user must click on
the worksheet to continue.

The problem is that I would like to execute another VBA subroutine after
the
value from the user form is returned into a worksheet cell, but now this
doesn't work.

I'd like to add that in this case, of course, I can call the other
subroutine after the user has closed the form, but I was thinking this as a
general problem, how to make the worksheet active after closing a user form.

Kaj B.

If you actually close the form and are not executing code from the form,
then the visible sheet should be active. That is the normal/default
behavior. Event calling it from a command button, doesn't seem to affect
this. Not sure how you are causing this problem.

--
Regards,
Tom Ogilvy

KajBre wrote in message
...
After this the worksheet is not active (selected), so a user must click
on
the worksheet to continue.

The problem is that I would like to execute another VBA subroutine after
the
value from the user form is returned into a worksheet cell, but now this
doesn't work.

I'd like to add that in this case, of course, I can call the other
subroutine after the user has closed the form, but I was thinking this as
a
general problem, how to make the worksheet active after closing a user
form.

Kaj B.

"Tom Ogilvy" kirjoitti viestissä
...
If you actually close the form and are not executing code from the form,
then the visible sheet should be active. That is the normal/default
behavior. Event calling it from a command button, doesn't seem to affect
this. Not sure how you are causing this problem.

This is a bit strange. I'm using Key Event for activating the sub routine,
which opens the user form like this:
' //
Application.OnKey "{F4}", "AsjaTuoteHaku" '
'..
Sub AsjaTuoteHaku() 'F4 pressed
'...
frmTuotteet.Show 'Show the Form
'.

' // back to the worksheet

Worksheets("Työmääräin").Activate
'..
End Sub

After this the worksheet is updated and visible, but *not* active. In my VBA
application I can handle this, but I'm just wondering, what is causing this
problem. Perhaps this has something to do with the ADO routines I'm using
for accessing data in a MS SQL Server database (before showing the user
form) ?

Kaj B.

Well a quick way to check would be to throw an IF block around your ADO
routines

If False then
' ado routines
End if

and see if it activates. However, I wouldn't expect ADO to cause that
problem, but one never knows.

--
Regards,
Tom Ogilvy

KajBre wrote in message
...
"Tom Ogilvy" kirjoitti viestissä
...
If you actually close the form and are not executing code from the form,
then the visible sheet should be active. That is the normal/default
behavior. Event calling it from a command button, doesn't seem to
affect
this. Not sure how you are causing this problem.

This is a bit strange. I'm using Key Event for activating the sub routine,
which opens the user form like this:
' //
Application.OnKey "{F4}", "AsjaTuoteHaku" '
'..
Sub AsjaTuoteHaku() 'F4 pressed
'...
frmTuotteet.Show 'Show the Form
'.

' // back to the worksheet

Worksheets("Työmääräin").Activate
'..
End Sub

After this the worksheet is updated and visible, but *not* active. In my
VBA
application I can handle this, but I'm just wondering, what is causing
this
problem. Perhaps this has something to do with the ADO routines I'm using
for accessing data in a MS SQL Server database (before showing the user
form) ?

Kaj B.

"Tom Ogilvy" kirjoitti viestissä
...
Well a quick way to check would be to throw an IF block around your ADO
routines

Problem solved.
The reason was in the way I called and showed the form.
'...
frmTuotteet.Show
frmTuotteet.lstTuotteet.SetFocus 'Points to a list box component in
the form
'...
The "SetFocus" method seemed to be the reason for the whole problem. I just
removed this line from the VBA code (-
frmTuotteet.lstTuotteet.SetFocus )

Kaj B.