event handling froma userform

**jad70** · 03-09-2009, 12:28 PM

Hi All,

I have a little problem with a userform commandbutton that I cannot resolve. I cannot seem to excecute a macro from a commandbutton, but if I go to the VBA project I can get it to work from there.

The cmb just calls a macro, which in itself call 5 other macros. This is the code for the button:

Private Sub CommandButton2_Click()
    Call Update_All 'this calls the update_all macro that updates all the figures on all tasks worksheet
    Unload UserForm3
End Sub

I may be missign something really simple here. As I said, if I go to the code and run it from there it opens the userform, I select a value and then click OK and it works, but it will not work if running from excel.
Any ideas?

JD

**Leith Ross** · 03-09-2009, 09:23 PM

Hello JD,

Your code as shown is correct. It probably is something simple. It will easier and faster to find the problem if you post your workbook.

**jad70** · 03-10-2009, 04:32 AM

Leith,

Attached is the workbook as is with only some text deleted.

My problem is this-
If I click on 'View All Tasks' on the info sheet, the sheet all tasks is shown. If you then delete the content in H3,4 and 5, then go back to the info sheet and click 'select Nation' the userform presents itself. If you select a Nation and then click OK, the values in H3,4 and 5 should appear. Currently they do not. But, if you select 'Update Tasks' and close the dataform, the cells values will re-appear, The close button on the dataform calls the same macro as the OK button on te Nation userform. I am a bit puzzled...
All the macros are described as to what they do.

Many thanks.
JD

**Andy Pope** · 03-10-2009, 05:11 AM

Your code references the Activesheet, which is "Infromation" when the code is run.

The simplest fix is to activate the correct sheet. The more robust solution is to alter your code to use full references.

Private Sub CommandButton2_Click()
    Worksheets("All Tasks").Activate
    Call Update_All  'this calls the update_all macro that updates all the figures on all tasks worksheet
    Unload UserForm3
    Worksheets("Information").Activate
End Sub

**jad70** · 03-10-2009, 09:15 AM

Andy,Leith

Apologies for not posting a reply earlier.

I came in this morning with a clear head and figured out that I had not selected the sheet for the code to run on. The reason it ran on the dataform closure is that the sheet was already activated.

I solved this a little differently, in that with the OK button I now have this:-

Private Sub CommandButton2_Click()
    Application.ScreenUpdating = False
    If Sheet1.Visible = True Then Sheet1.Visible = False
    Call Update_All   'this calls the update_all macro that updates all the figures on all tasks worksheet
    Unload UserForm3
    Application.ScreenUpdating = True
End Sub

The reason to make the sheet hidden is because however I configure the screen updating, if I select another option button with sheet1 visible the screeen jumps to sheet1 and then back again. Might not be perfect but it works.
and for the All Update code I have this:-

Sub Update_All()  'this code just call all the above code for either the manual input on the Information page, or the OK
                  'button on the Select Nation userform.
                  
Sheet1.Activate
With ActiveSheet
 Call FHtoCal
 Call FCtoCal
 Call EHtoCal
 Call APUCYtoCal
 Call APUHrstoCal
End With
End Sub

It has the same result though.

Many thanks again.

JD