VBA variable Userform to Module

[leatherhen99]

Hi All,

I have looked at so many different examples, but I'm not able to relate it back to my code. Here's what I have...

I was using an input box, but if the user spells something incorrectly, it could pose an issue... so I decided to use a form with radio buttons.

Please provide any feedback that seems off...

Module:

Public addmonth As String

Public Sub Setup()
'
' Setup Macro

'show Month Template tab
    Worksheets("Month Template").Visible = True
'create a copy of the monthly template
    Sheets("Month Template").Copy after:=Worksheets(Sheets.Count)
'ask user for the month being created; enter into cell A1
Static iba As String
    With Enter_Month
        .Show
     End With

Enter_Month Form:

Private Sub CommandButton1_Click()

Dim addmonth As String
If Enter_Month.OptionButton1.Value = True Then
    addmonth = "January"
ElseIf Enter_Month.OptionButton2.Value = True Then
    addmonth = "February"
ElseIf Enter_Month.OptionButton3.Value = True Then
    addmonth = "March"
ElseIf Enter_Month.OptionButton4.Value = True Then
    addmonth = "April"
ElseIf Enter_Month.OptionButton5.Value = True Then
    addmonth = "May"
ElseIf Enter_Month.OptionButton6.Value = True Then
    addmonth = "June"
ElseIf Enter_Month.OptionButton7.Value = True Then
    addmonth = "July"
ElseIf Enter_Month.OptionButton8.Value = True Then
    addmonth = "August"
ElseIf Enter_Month.OptionButton9.Value = True Then
    addmonth = "September"
ElseIf Enter_Month.OptionButton10.Value = True Then
    addmonth = "October"
ElseIf Enter_Month.OptionButton11.Value = True Then
    addmonth = "November"
ElseIf Enter_Month.OptionButton12.Value = True Then
    addmonth = "December"
End If
Range("A1").Value = addmonth

'rename the table with the initial input box
    With ActiveSheet
        .ListObjects(1).Name = addmonth
        
    End With
'enter month and year into A6
Range("A6").Value = addmonth & Year(Date)

Me.Hide

I would like the addmonth to be available in the remainder of the module code...but I can't figure out how to get it over there...

Any advice would be greatly appreciated!!!

Thanks,
Heather

 

[dmt32]

Hi,
try following update to your codes & see if does what you want

Place all following codes in your userforms code page

Private Sub CommandButton1_Click()
    Dim i As Integer
    For i = 1 To 12
        If Me.Controls("OptionButton" & i).Value Then Exit For
    Next
    Me.Tag = MonthName(i, False)
    Me.Hide

End Sub

Function GetMonth() As Variant
    Me.Show
    GetMonth = IIf(IsNumeric(Me.Tag), Val(Me.Tag), Me.Tag)
    Unload Me
End Function

Private Sub CommandButton2_Click()
'Cancel Button
    Me.Tag = vbCancel
    Me.Hide
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Cancel = CloseMode = 0
    If Cancel Then Call CommandButton2_Click
End Sub

I have included CommandButton2 which is a Cancel Button - If not already done so, you will need to add the button on your form

To call your for your procedure will need to be updated as follows

Public Sub Setup()
Dim addmonth As Variant
'
' Setup Macro

'show Month Template tab
    Worksheets("Month Template").Visible = True
'create a copy of the monthly template
    Sheets("Month Template").Copy after:=Worksheets(Sheets.Count)
'ask user for the month being created; enter into cell A1

'display Enter_Month form
    addmonth = Enter_Month.GetMonth
'cancel pressed
    If Val(addmonth) = vbCancel Then Exit Sub
  
  Range("A1").Value = addmonth

'rename the table with the initial input box
    With ActiveSheet
        .ListObjects(1).Name = addmonth
     
    End With
'enter month and year into A6
Range("A6").Value = addmonth & Year(Date)

  
End Sub

Solution could be adapted for the form to show current month when first displayed in needed - just include following code in your userform

Private Sub UserForm_Initialize()
    Me.Controls("OptionButton" & Month(Date)).Value = True
End Sub

Hope Helpful

Dave

 

[leatherhen99]

Hey Dave,

It worked!!!

Now I'm going to try to break down your code and figure out how I can do better at writing my code in the future... it's kinda new to me and I'm still a bit awful at not writing the unnecessary coding...

Thanks again! I really appreciate it!!!

 

[dmt32]

Hey Dave,

It worked!!!

Now I'm going to try to break down your code and figure out how I can do better at writing my code in the future... it's kinda new to me and I'm still a bit awful at not writing the unnecessary coding...

Thanks again! I really appreciate it!!!

Solution not that complex & uses builtin function MonthName which saves all that hard coding for the months.
Glad worked ok for you & thanks for feedback

Dave