VBA Userform - Using data entered to update worksheets

[VorLag]

Hello all. I have an email that I get every month that has a couple of numbers in it that need to go into 10 different worksheets. I know this would be easier to do in Access, but I'm updating sheets used by other people and don't have the option to change the current process (for now). Instead, I'm writing a macro to just update these worksheets with these couple of numbers so that I don't have to do it manually.

I CAN achieve the exact thing I'm doing by just using 3 input boxes, but I like the look of the userform since the inputs would all be on one window. I would like to know how to adjust this bit of code I've found elsewhere online. This is supposed to validate that each input is filled in, which would be very helpful. This part works, but the problem I'm having is that once I enter the data and hit the Command button, the window doesn't go away and I don't know how to make it do that. I've tried the .Hide command, but that doesn't seem to do anything to make the rest of the Macro execute.

So, the userform is named ScoreUserform

This is the code I found online:

Private Sub CommandButton1_Click()
    'validate first three controls have been entered...
    If Me.ReportMon.Text = Empty Then 'Firstname
        MsgBox "Please enter Report Month.", vbExclamation
        Me.ReportMon.SetFocus 'position cursor to try again
        Exit Sub 'terminate here - why continue?
    End If
    If Me.MonthScore.Text = Empty Then 'Surname
        MsgBox "Please enter the BMTM score for the month.", vbExclamation
        Me.MonthScore.SetFocus 'position cursor to try again
        Exit Sub 'terminate here - why continue?
    End If
    If Me.YearScore.Text = Empty Then 'Department
        MsgBox "Please enter the BMTM score for YTD.", vbExclamation
        Me.YearScore.SetFocus 'position cursor to try again
        Exit Sub 'terminate here - why continue?
    End If
End Sub

And my experimental bits to try to update worksheets:

Sub Update()
ws(1) = "Test1.xlsx"
ws(2) = "Test2.xlsx"

path = "C:/Test/"

ScoreUserform.Show

ReportMon = ScoreUserform.ReportMon.Text
MonScore = ScoreUserform.MonthScore.Value
YearScore = ScoreUserform.YearScore.Value

For x = 1 to 2

OpenWs = path & ws(x)
Workbooks.Open (OpenWs)
Worbooks.Ws(x).Activate

Range("A1").Select
ActiveCell.Value = MonScore
Range("B1").Select 
ActiveCell.Value = YearScore

ActiveWorkbook.Save
ActiveWorkbook.Close
Next

End Sub

The problem of course is that I am definitely missing something because the userform doesn't go anywhere and nothing is entered. Can someone tell me what I'm missing?

 

[NeonRedSharpie]

Private Sub CommandButton1_Click()    'validate first three controls have been entered...
    If Me.ReportMon.Text = Empty Then 'Firstname
        MsgBox "Please enter Report Month.", vbExclamation
        Me.ReportMon.SetFocus 'position cursor to try again
        Exit Sub 'terminate here - why continue?
    End If
    If Me.MonthScore.Text = Empty Then 'Surname
        MsgBox "Please enter the BMTM score for the month.", vbExclamation
        Me.MonthScore.SetFocus 'position cursor to try again
        Exit Sub 'terminate here - why continue?
    End If
    If Me.YearScore.Text = Empty Then 'Department
        MsgBox "Please enter the BMTM score for YTD.", vbExclamation
        Me.YearScore.SetFocus 'position cursor to try again
        Exit Sub 'terminate here - why continue?
    End If
    
    Me.Hide
    
    Ws(1) = "Test1.xlsx"
    Ws(2) = "Test2.xlsx"
    
    Path = "C:/Test/"
        
    ReportMon = Me.ReportMon.Text
    MonScore = Me.MonthScore.Value
    YearScore = Me.YearScore.Value
    
    For x = 1 To 2
    
        OpenWs = Path & Ws(x)
        Workbooks.Open (OpenWs)
        Worbooks.Ws(x).Activate
        
        Range("A1").Select
        ActiveCell.Value = MonScore
        Range("B1").Select
        ActiveCell.Value = YearScore
        
        ActiveWorkbook.Save
        ActiveWorkbook.Close
    Next x
    
    
End Sub

You can just put it all in one code. Then, your "Update" code is simply:

Sub update()
     ScoreUserform.Show
end sub

I would also look at clearing or unloading the userform after everything is run so that on the next call it is empty. You can also put an initialize code in there. But first step is to make sure this button process works.

 

[ranman256]

(see if any of these help)
Workbooks.Open (OpenWs)...needs only to be opened once. Yours is in a loop.
Activate the workbook: workbooks(wbName).activate
worksheets(x).select


You can also validate by this:
if IsValidForm() then
'run macro
else
'user must correct form
endif

public Function IsValidForm() as boolean
dim vMsg
  select case true
       case txtName = "" 
          vMsg = "Client Name is missing"
       case isnull(cboState )
          vMsg = "State is missing"
         cboState.setfocus
  end select
  if vMsg <>"" then msgbox vmsg,vbCritical,"Required"
  IsValidForm =vMsg =""
end sub

[/code]

 

[VorLag]

(see if any of these help)
Workbooks.Open (OpenWs)...needs only to be opened once. Yours is in a loop.
Activate the workbook: workbooks(wbName).activate
worksheets(x).select


You can also validate by this:
if IsValidForm() then
'run macro
else
'user must correct form
endif

public Function IsValidForm() as boolean
dim vMsg
  select case true
       case txtName = "" 
          vMsg = "Client Name is missing"
       case isnull(cboState )
          vMsg = "State is missing"
         cboState.setfocus
  end select
  if vMsg <>"" then msgbox vmsg,vbCritical,"Required"
  IsValidForm =vMsg =""
end sub

[/code]

The Open command is a loop because this is going to open and close several worksheets. That part isn't a mistake and isn't the reason why I'm having trouble with the userform. You'll see I also included a Save and Close command at the end of the loop.

 

[VorLag]

...

I would also look at clearing or unloading the userform after everything is run so that on the next call it is empty. You can also put an initialize code in there. But first step is to make sure this button process works.

I didn't know that you could combine it, but you're right, that actually works. That was even simpler than I thought it would be. Lol. Thanks!