How to trigger a Form from a Macro and get the inputs from a Form into VBA Module Code

user_13644020 0
Viewed 86

I have a macro where I would like to create Custom Buttons to the Message Box, and I thought of triggering a form that looks like a Msgbox with options like "YTD", "Specific Months". Here's the below form I have created:

enter image description here

And the code inside the form is as below:

Option Explicit
Public InputMsg As String

Public Sub CommandButton1_Click()
InputMsg = "YTD"

End Sub

Public Sub CommandButton2_Click()
InputMsg = "Specific Months"

I would like to get these Input Values to the Module Code which I mentioned below:

Sub Chts_Functions_UB()

'CallingForms
On Error Resume Next
Application.DisplayAlerts = False
Set Wb = ThisWorkbook
Set WsCharts = Wb.Sheets("Trend Charts")
Set UBMainChart = WsCharts.ChartObjects("UBMainChart")
Set UBMonthlyYTDSht = Wb.Worksheets("UM - Monthly & YTD")
Set FPFAChart = WsCharts.ChartObjects("FP_FA_YTD Chart")
Set FPBPChart = WsCharts.ChartObjects("FP_BP_YTD Chart")
Set FPRMDChart = WsCharts.ChartObjects("FP_RMD_YTD Chart")
Set FPMonthlyYTDSht = Wb.Worksheets("FP - Monthly & YTD")
YearValue = WsCharts.Range("A1").Value
'btnFunctionName = WsCharts.Shapes(Application.Caller).Name
WsCharts.Range("F2").Value = btnFunctionName

Dim Crows As Long, Ccols As Long
Dim NamedRng As Variant
'****Here I would like to get the Input from the Form (YTD or Specific Months Button)***
Crows = UBMonthlyYTDSht.Range("A" & Rows.Count).End(xlUp).Row
Ccols = UBMonthlyYTDSht.Cells(1, Columns.Count).End(xlToLeft).Column
On Error GoTo 0

Appreciate your help!!

userform vba excel
1 Answers

Avoid the public variables until and unless it is absolutely necessary. Pass the relevant value as a parameter as shown below.

Your form code

Private Sub CommandButton1_Click()
    Chts_Functions_UB "YTD"
End Sub

Private Sub CommandButton2_Click()
    Chts_Functions_UB "Specific Months"
End Sub

Your module code

Sub Chts_Functions_UB(ChartType As String)
    '
    ' Chts_Functions_UB code here
    '
End Sub

Also avoid the use of On Error Resume Next. Use it judiciously and use proper error handling. For example

Sub Chts_Functions_UB(ChartType As String)
    On Error GoTo Whoa
    
    Application.DisplayAlerts = False

    '
    ' Chts_Functions_UB code here
    '
    
LetsContinue:
    Application.DisplayAlerts = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

Note: LetsContinue and Whoa are names that I like to use. You can give them names that you like.

user_11405790
Related