Userform VBA

[ldarley]

Hi,

I've created a simple userform to update data held in a table. I'm trying to execute the code from a command button on a 'menu' worksheet. My hope was that the form would pop up on the menu worksheet and I could update the table from there. However when I run the macro it moves to the LookUp worksheet with the form on it, I guess through the Sheets...activate line. I had thought disabling screen updating would prevent this happening and allow the form to popup on the menu worksheet but obviously not!

How would I be able to modify the code I have so that I am able to update the table on the lookUp worksheet without visually moving to that sheet?

The form macro command is held in module 1 and the Initialise code I have is below, thanks in advance for any help!


Private Sub Userform_Initialize()


Application.ScreenUpdating = False
Application.EnableEvents = False


Sheets("LookUp").Activate
Range("GM6").Select


UserForm1.txtBrand.Value = Sheets("LookUP").Range("GM6").Value
UserForm1.txtSick.Value = Sheets("LookUP").Range("GN6").Value
UserForm1.txtRestricted.Value = Sheets("LookUP").Range("GO6").Value
UserForm1.txtRoute.Value = Sheets("LookUP").Range("GP6").Value
UserForm1.txtOther.Value = Sheets("LookUP").Range("GQ6").Value
UserForm1.txtRDW.Value = Sheets("LookUP").Range("GR6").Value
UserForm1.txtFailures.Value = Sheets("LookUP").Range("GS6").Value
UserForm1.txtMins.Value = Sheets("LookUP").Range("GT6").Value
UserForm1.txtCancel.Value = Sheets("LookUP").Range("GU6").Value


Application.ScreenUpdating = True
Application.EnableEvents = True


End Sub

 

[Fluff]

Ok, I'd recommend removing the Update Sub & the call to it, add a command button to the form & use this

Private Sub CommandButton1_Click()
   With Sheets("LookUp").Range("A" & i)
      .Offset(0, 1).Value = UserForm1.txtSick.Text
      .Offset(0, 2).Value = UserForm1.txtRestricted.Text
      .Offset(0, 3).Value = UserForm1.txtRoute.Text
      .Offset(0, 4).Value = UserForm1.txtOther.Text
      .Offset(0, 5).Value = UserForm1.txtRDW.Text
      .Offset(0, 6).Value = UserForm1.txtFailures.Text
      .Offset(0, 7).Value = UserForm1.txtMins.Text
      .Offset(0, 8).Value = UserForm1.txtCancel.Text
   End With

End Sub

 

[ldarley]

Got it, thanks for persevering with this, I never anticipated that it would require so much change! Really appreciate the help!

 

[Fluff]

Glad to help & thanks for the feebdack