CODEWITHSUNDEEP
vbams-word
VBAbyMBA
VBAbyMBA

Reputation: 826

Use userform value in Normal macros

I have user form 'CemeaFinallist' in which there are checkbox and button. I want to use checkbox Name's value as a Variable=CNN in 'Normal.newmacros.minipro'

enter image description here

Following is userform button script

Private Sub Shift_Click()

CemeaFinallist.Hide
Dim ctl As Control
Dim j As Long
For Each ctl In Me.Controls
If TypeOf ctl Is MSForms.CheckBox Then
    If Me.Controls(ctl.Name).Value = True Then

If ctl.Caption = "Select All" Then
Else

Application.Run MacroName:="Normal.NewMacros.minipro"

End If
End If
End If

Next
Application.ScreenUpdating = True
End Sub

following is Normal.NewMacros macro

Sub MiniPRO()
Application.ScreenUpdating = False
Dim path As String
Dim CNN As String
Dim ex As String
Dim News As String
Dim SD As String


path = "C:\Documents and Settings\Administrator\Desktop\EMEA CEEMEA\EMEA FOR DAILY USE\"
CNN = ctl.Name 'at this stage Run Time Error '424' Object required'
ex = ".DOCX"

Documents.Open FileName:=path & CNN & ex

Upvotes: 0

Views: 178

Answers (1)

Vincent G
Vincent G

Reputation: 3188

In your UserForm, use:

Application.Run MacroName:="NewMacros.MiniPRO", varg1:=ctl.Name

In Normal.NewMacros module, use:

Function MiniPRO(ByVal CtlName as String)
    Application.ScreenUpdating = False
    Dim path As String
    Dim CNN As String
    Dim ex As String
    Dim News As String
    Dim SD As String


    path = "C:\Documents and Settings\Administrator\Desktop\EMEA CEEMEA\EMEA FOR DAILY USE\"
    CNN = CtlName
    ex = ".DOCX"

    Documents.Open FileName:=path & CNN & ex
    '...
End Function

You can also replace the test If Me.Controls(ctl.Name).Value = True Then by the simpler: If ctl.value = True Then since you already have a reference to the control.

Upvotes: 1

Related Questions