Activate Excel worksheet after showing a user form ?

  • Thread starter Thread starter KajBre
  • Start date Start date
K

KajBre

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.
Click to expand...
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.
 
Tom Ogilvy said:
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.
Click to expand...
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.
 
Tom Ogilvy said:
Well a quick way to check would be to throw an IF block around your ADO
routines
Click to expand...
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.
 
Back
Top