Use userform value in Normal macros

Question

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'

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

you did not declare ctl, so its type is Variant , same as Dim ctl as Variant. you have not assigned any value to it, but you are trying to use it as an object by retrieving a Name property. error says that there is no object to be found. you need to define the ctl object in MiniPRO dim ctl as object set ctl = forms("CemeaFinallist").checkbox .... not the exact code but something along these lines — jsotola
– jsotola, Commented Aug 9, 2017 at 7:24
There is no way to directly access a local variable of a Sub from an another Sub. You can either use Gobal variables (not recommended), or even better replace your macro Sub with a macro Function and pass arguments to the function in the Application.Run call. — Vincent G
– Vincent G, Commented Aug 9, 2017 at 7:26
@VincentG you got my point. exactly I need to use a checkbox Name or Value as variable from another sub. kindly elaborate how do i fix in above mention code? — VBAbyMBA
– VBAbyMBA, Commented Aug 9, 2017 at 7:32

Vincent G · Accepted Answer · 2017-08-09 09:17:31Z

1

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.

edited Aug 9, 2017 at 9:17

answered Aug 9, 2017 at 7:32

Vincent G

3,1981 gold badge16 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

VBAbyMBA Over a year ago

compile error: Syntax error at 'Application.Run MacroName:="Normal.NewMacros.MiniPRO", ctl.Name'

Vincent G Over a year ago

Ok, might be because the first argument was explicit, and the second not. Try Application.Run MacroName:="Normal.NewMacros.MiniPRO", varg1:=ctl.Name

VBAbyMBA Over a year ago

I had added whole code which i have been using in Userform kindly take a look

Vincent G Over a year ago

I don't use lots of macros in Word, but it seems that specifying the template is not working very well (see this question ). I will correct the code above. BTW, do you want the control name or the control caption?

Vincent G Over a year ago

I don't think you can have controls named with space like "Saudi Arabia" or "Cote D Ivoire"

|

Collectives™ on Stack Overflow

Use userform value in Normal macros

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related