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, Aug 09 '17 at 07: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, Aug 09 '17 at 07: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, Aug 09 '17 at 07:32
@VincentG well its a good question. Unintentionally doing it . may be all my macros are in normal. or may be the minipro is very long. There should be a way doing it? — VBAbyMBA, Aug 09 '17 at 08:41
When registering your macro, you are asked if the macro should be available from any word file (All document), it is the default setting. If you keep it, the macro will be stored in "normal.dotm". but if you want to distribute your document it is better to select to store the macro in your document instead of normal.dotm. — Vincent G, Aug 09 '17 at 08:52
You can also just create modules in your project and add the functions and subs there. — Vincent G, Aug 09 '17 at 08:52
@VincentG I have added whole **Minipro** code to **Userform** instead of `Application.Run MacroName:="Normal.NewMacros.minipro"` and made some changes `CNN = ctl.Caption`. its working fine. thanks — VBAbyMBA, Aug 09 '17 at 09:23

Vincent G · Accepted Answer · 2017-08-09T09:17:31.980

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 09 '17 at 09:17

answered Aug 09 '17 at 07:32

Vincent G

3,153
1
13
30

compile error: Syntax error at 'Application.Run MacroName:="Normal.NewMacros.MiniPRO", ctl.Name' – VBAbyMBA Aug 09 '17 at 07:38
Ok, might be because the first argument was explicit, and the second not. Try `Application.Run MacroName:="Normal.NewMacros.MiniPRO", varg1:=ctl.Name` – Vincent G Aug 09 '17 at 07:45
I had added whole code which i have been using in Userform kindly take a look – VBAbyMBA Aug 09 '17 at 07:47
I don't use lots of macros in Word, but it seems that specifying the template is not working very well (see [this question](https://stackoverflow.com/questions/8015688/cant-call-word-vba-macro-with-parameters) ). I will correct the code above. BTW, do you want the control name or the control caption? – Vincent G Aug 09 '17 at 08:44
1

I don't think you can have controls named with space like "Saudi Arabia" or "Cote D Ivoire" – Vincent G Aug 09 '17 at 08:54
Sorry! I'm having trouble while debugging, How can I use `Call` (To run a macro) instead of `Application.Run` while using `ctl.Name` from the user-form? kindly help. – VBAbyMBA Apr 25 '18 at 07:45
Don't use `Call`. Just call it like any other function. (Given that it is a `Public` function. – Vincent G Apr 26 '18 at 11:25

Use userform value in Normal macros

1 Answers1