Public variable in more macros

[Rataplan]

Good morning.
I would like to point out that I am new to programming with VBA and in general I am not a professional programmer.
Let's get to the question: I am creating an excel worksheet to record and monitor maintenance interventions. I would like to create a macro, with different Forms, to populate the excel Worksheet and analyze the data. My need is to pass the value of the "Scelta" variable to the "Userform1" macro to use it as the 'rowsource' property of the "cbo_Ricerca" combobox so that I am listed with the available search ranges, which I have created on the sheet with names, as you can see in the files that I am attaching.
Thank you for your patience and availability.

 

[jkpieterse]

Here is the minimum code needed in the userform so you can pass it the scelta range and have the combobox get filled with its contents:

Option Explicit

Public SceltaRng As Range  'This is the public variable that holds the scelta range

'We are not using userform_Initialize, as that runs too soon, rather we define an initialize routine we call explicitly
Public Sub Initialize()
    'Set the list to the content of scelta
    If Not SceltaRng Is Nothing Then
        cbo_Ricerca.List = SceltaRng.Value
    End If
End Sub

The code that loads the form needs to change to:

Sub ShowaForm()
    With UserForm1
        Set .SceltaRng = ThisWorkbook.Names("Scelta").RefersToRange
        .Initialize
        .Show
    End With
End Sub

 

[Rataplan]

Here is the minimum code needed in the userform so you can pass it the scelta range and have the combobox get filled with its contents:

Option Explicit

Public SceltaRng As Range  'This is the public variable that holds the scelta range

'We are not using userform_Initialize, as that runs too soon, rather we define an initialize routine we call explicitly
Public Sub Initialize()
    'Set the list to the content of scelta
    If Not SceltaRng Is Nothing Then
        cbo_Ricerca.List = SceltaRng.Value
    End If
End Sub

The code that loads the form needs to change to:

Sub ShowaForm()
    With UserForm1
        Set .SceltaRng = ThisWorkbook.Names("Scelta").RefersToRange
        .Initialize
        .Show
    End With
End Sub

Thanks, @jkpieterse for your prompt reply.
as I explained to you I am quite newbie to excel macros.
I would ask you if you can tell me where to insert the code you suggested in the code files I published.
I tried to insert it, but maybe I am in the wrong file where to copy it.
Guido

 

[jkpieterse]

Well, the first code block goes into each userform module. Make sure you check if the form as a sub called Userform_Initialize. Move that code to my Initialize sub as shown above. For the second block you must look in your own code for code like "userform1.Show" and replace that with the code inside the sub ShowaForm I posted above.
It would help if you post the actual code rather than screen-shots, using the little code buttons above the message editor.

 

[Rataplan]

Hi,@jkpieterse.
This is the link to the worksheet in question.
cannot see the "the little code buttons above the message editor"... I'm very newbie...
Sorry...
Thanks so much...

Calendario attività 1 .xlsm

drive.google.com

 

[HighAndWilder]

Hi,@jkpieterse.
This is the link to the worksheet in question.
cannot see the "the little code buttons above the message editor"... I'm very newbie...
Sorry...
Thanks so much...

Calendario attività 1 .xlsm

drive.google.com

See image attached to show you where the VBA 'Quick-wrap selection as VBA code' option is.