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

 

[ldarley]

Both

 

[Fluff]

Could you post the entire code from the userform module. Using code tags, the # icon in the reply window.

 

[ldarley]

This is in Module1

Sub DisplayWeightingsForm()

    UserForm1.Show


End Sub

This is in the Userform1:

Private Sub SpinButton1_SpinDown()If i > 6 Then i = i - 1 Else i = 6
With Sheets("LookUp").Range("GM" & i)
    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


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


Call Update
With Sheets("LookUp").Range("GM" & i)
    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


Private Sub Userform_Initialize()


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


End Sub




Sub Update()


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


End Sub

 

[Fluff]

Ok, in post#6 I failed to put a couple of lines of code.
Add this to the very top of the module (before any code)

Option Explicit
Dim i As Long

You'll also need to amend the Update sub like

Sub Update()
   With Sheets("LookUp").Range("GM" & i)
      .Offset(0, 1).Value = UserForm1.txtSick.Text
      .Offset(0, 2).Value = UserForm1.txtRestricted.Text
      .Offset(0, 3).Value = UserForm1.txtRoute.Text
   End With
End Sub

 

[ldarley]

The last change seems to overwrite the cells in the table, previously the update sub only changed something if I changed the text?

 

[Fluff]

Your original code for the spinUp was

Private Sub SpinButton1_SpinUp()

Call Update
ActiveCell.Offset(1, 0).Select
UserForm1.txtBrand = ActiveCell.Offset(0, 0).Text

Which is calling the update sub whenever you hit the button, regardless of any changes

 

[ldarley]

I think that's right it calls the update so if there is no change then the values remain the same as currently stated in that row, however if I change the text entry to any of the textboxes it updates the table. It seems to work in the old version, except as discussed from the OP it goes to the lookup worksheet to run.

Now the form runs in the menu worksheet, when I hit the spin button the value in txtbrand cycles through the rows but the others stay the same, when I look at the table its taken the entry's from row one and changed the values to match in the other rows.

 

[Fluff]

Try moving the

Call Update

line to the end of the sub.
If that doesn't work,would you be willing to put your workbook on a file share site such as OneDrive, Dropbox, GoogleDrive & supply a link ?

 

[ldarley]

Sent you a PM Fluff

 

[ldarley]

https://1drv.ms/x/s!Ahd25-gdj8wPgRmHo4LpbfdJzAJe

Hope this works ok