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]

Simply remove these 2 lines

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

 

[ldarley]

lol sorry Fluff, I've made an obvious mistake in my question, the form also has spinbuttons to go up and down the table so I used the sheet.activate and range lines to set the start reference to move up and down the table, so if I remove those two lines I just move up and down my menu sheet. I'm guessing there is an alternative to the activecell command below if I remove those two lines, or is there another alternative?

Spin button code:

Private Sub SpinButton1_SpinUp()




Call Update
ActiveCell.Offset(1, 0).Select
UserForm1.txtBrand = ActiveCell.Offset(0, 0).Text
UserForm1.txtSick = ActiveCell.Offset(0, 1).Text
UserForm1.txtRestricted = ActiveCell.Offset(0, 2).Text
UserForm1.txtRoute = ActiveCell.Offset(0, 3).Text
UserForm1.txtOther = ActiveCell.Offset(0, 4).Text
UserForm1.txtRDW = ActiveCell.Offset(0, 5).Text
UserForm1.txtFailures = ActiveCell.Offset(0, 6).Text
UserForm1.txtMins = ActiveCell.Offset(0, 7).Text
UserForm1.txtCancel = ActiveCell.Offset(0, 8).Text

End Sub

 

[Fluff]

How about

Private Sub SpinButton1_SpinUp()

Static i As Long
i = i + 1

Call Update
With Sheets("LookUp").Range("G6").Offset(i, 0)
   UserForm1.txtBrand = .Offset(0, 0).Text
   UserForm1.txtSick = .Offset(0, 1).Text
   UserForm1.txtRestricted = .Offset(0, 2).Text
   UserForm1.txtRoute = .Offset(0, 3).Text
   UserForm1.txtOther = .Offset(0, 4).Text
   UserForm1.txtRDW = .Offset(0, 5).Text
   UserForm1.txtFailures = .Offset(0, 6).Text
   UserForm1.txtMins = .Offset(0, 7).Text
   UserForm1.txtCancel = .Offset(0, 8).Text
End With

End Sub

 

[ldarley]

Thanks Fluff, I'm getting closer, how do I pull the variable i into a spindown routine so that I go the opposite way from the current point?

 

[Fluff]

Add this to the very top of the module (before any code)
Remove this line

Static i As Long

and then use

Private Sub SpinButton1_SpinDown()
If i > 6 Then i = i - 1 Else i = 6
With Sheets("Lookup").Range("G" & i)
   MsgBox .Address
End With
End Sub

Private Sub SpinButton1_SpinUp()
If i < 6 Then i = 6 Else i = i + 1

Call Update
With Sheets("lookup").Range("G" & i)
   MsgBox .Address

 

[ldarley]

Thanks for helping but I'm not sure I follow that, it just throws a cell address out to me via a message box, this is what I now have in the form code:


Private Sub SpinButton1_SpinDown()
If i > 6 Then i = i - 1 Else i = 6
With Sheets("Lookup").Range("GM" & i)
MsgBox .Address
End With
End Sub


Private Sub SpinButton1_SpinUp()
If i < 6 Then i = 6 Else i = i + 1


Call Update
With Sheets("lookup").Range("GM" & i)
MsgBox .Address
UserForm1.txtBrand = .Offset(0, 0).Text
UserForm1.txtSick = .Offset(0, 1).Text
UserForm1.txtRestricted = .Offset(0, 2).Text
UserForm1.txtRoute = .Offset(0, 3).Text
UserForm1.txtOther = .Offset(0, 4).Text
UserForm1.txtRDW = .Offset(0, 5).Text
UserForm1.txtFailures = .Offset(0, 6).Text
UserForm1.txtMins = .Offset(0, 7).Text
UserForm1.txtCancel = .Offset(0, 8).Text
End With


End Sub

 

[Fluff]

Remove the Msgbox, I just had it in there as a test.
For the spin_down you'll need to add in the rest of your code.

 

[ldarley]

I've taken out the msg box but now when I hit the spinner the text value in the text boxes doesn't update?

 

[Fluff]

Is that on both Up & Down or just one of them?