Chiamare una userform da un sottogruppo specifico

Un'altra domanda newbie ma non posso trovare la mia risposta finora …

Ho una cartella di lavoro con più fogli, li chiami S1, S2 ecc. Ho una userform che fa un'operazione che può essere triggersta da qualsiasi foglio.

Il mio problema qui è che ho i parametri passati alla userform dal sub

Public c As Integer, lf As Integer, ld As Integer Sub Tri() ld = 8 lf = 128 Application.ScreenUpdating = False UsForm.Show End Sub 

Ora la mia cartella di lavoro sta crescendo in size e le differenze appaiono da S1 a S2 etc che mi richiedono di cambiare i parametri a seconda del foglio che viene lanciato da. Quindi ho rimosso il mio codice da "module" e inserito nella parte "Microsoft excel object". Ma ora sembra che non abbia accesso alle mie variables pubbliche e appena richiesto ld o lf, viene visualizzato come vuoto (anche se è stato implementato nella precedente forma di utente).

Per favore, può qualcuno dirmi cosa mi manca? Come posso fare altrimenti (non voglio mettere i dati nei fogli stessi)?

Devi approfittare del fatto che una userform è una class. Quindi, come esempio, aggiungere il seguente codice al "module". Supponiamo che tu abbia un button con il nome CommandButton1

 Option Explicit Dim mVar1 As Long Dim mVar2 As String Property Let Var1(nVal As Long) mVar1 = nVal End Property Property Let Var2(nVal As String) mVar2 = nVal End Property Private Sub CommandButton1_Click() MsgBox mVar1 & " - " & mVar2 Me.Hide End Sub Opzione esplicita Option Explicit Dim mVar1 As Long Dim mVar2 As String Property Let Var1(nVal As Long) mVar1 = nVal End Property Property Let Var2(nVal As String) mVar2 = nVal End Property Private Sub CommandButton1_Click() MsgBox mVar1 & " - " & mVar2 Me.Hide End Sub Dim mVar1 As Long Option Explicit Dim mVar1 As Long Dim mVar2 As String Property Let Var1(nVal As Long) mVar1 = nVal End Property Property Let Var2(nVal As String) mVar2 = nVal End Property Private Sub CommandButton1_Click() MsgBox mVar1 & " - " & mVar2 Me.Hide End Sub Dim mVar2 come string Option Explicit Dim mVar1 As Long Dim mVar2 As String Property Let Var1(nVal As Long) mVar1 = nVal End Property Property Let Var2(nVal As String) mVar2 = nVal End Property Private Sub CommandButton1_Click() MsgBox mVar1 & " - " & mVar2 Me.Hide End Sub Proprietà Let Var1 (nVal As Long) Option Explicit Dim mVar1 As Long Dim mVar2 As String Property Let Var1(nVal As Long) mVar1 = nVal End Property Property Let Var2(nVal As String) mVar2 = nVal End Property Private Sub CommandButton1_Click() MsgBox mVar1 & " - " & mVar2 Me.Hide End Sub mVar1 = nVal Option Explicit Dim mVar1 As Long Dim mVar2 As String Property Let Var1(nVal As Long) mVar1 = nVal End Property Property Let Var2(nVal As String) mVar2 = nVal End Property Private Sub CommandButton1_Click() MsgBox mVar1 & " - " & mVar2 Me.Hide End Sub Proprietà Let Var2 (nVal come string) Option Explicit Dim mVar1 As Long Dim mVar2 As String Property Let Var1(nVal As Long) mVar1 = nVal End Property Property Let Var2(nVal As String) mVar2 = nVal End Property Private Sub CommandButton1_Click() MsgBox mVar1 & " - " & mVar2 Me.Hide End Sub mVar2 = nVal Option Explicit Dim mVar1 As Long Dim mVar2 As String Property Let Var1(nVal As Long) mVar1 = nVal End Property Property Let Var2(nVal As String) mVar2 = nVal End Property Private Sub CommandButton1_Click() MsgBox mVar1 & " - " & mVar2 Me.Hide End Sub MsgBox mVar1 & "-" & mVar2 Option Explicit Dim mVar1 As Long Dim mVar2 As String Property Let Var1(nVal As Long) mVar1 = nVal End Property Property Let Var2(nVal As String) mVar2 = nVal End Property Private Sub CommandButton1_Click() MsgBox mVar1 & " - " & mVar2 Me.Hide End Sub 

Quindi puoi aggiungere un module normale

 Sub TestForm() Dim frm As UserForm1 Set frm = New UserForm1 Load frm frm.Var1 = 42 frm.Var2 = "Test" frm.Show Unload frm End Sub Sub TestForm () Sub TestForm() Dim frm As UserForm1 Set frm = New UserForm1 Load frm frm.Var1 = 42 frm.Var2 = "Test" frm.Show Unload frm End Sub Dim frm Come UserForm1 Sub TestForm() Dim frm As UserForm1 Set frm = New UserForm1 Load frm frm.Var1 = 42 frm.Var2 = "Test" frm.Show Unload frm End Sub Impostare frm = Nuovo UserForm1 Sub TestForm() Dim frm As UserForm1 Set frm = New UserForm1 Load frm frm.Var1 = 42 frm.Var2 = "Test" frm.Show Unload frm End Sub Carica frm Sub TestForm() Dim frm As UserForm1 Set frm = New UserForm1 Load frm frm.Var1 = 42 frm.Var2 = "Test" frm.Show Unload frm End Sub frm.Var1 = 42 Sub TestForm() Dim frm As UserForm1 Set frm = New UserForm1 Load frm frm.Var1 = 42 frm.Var2 = "Test" frm.Show Unload frm End Sub frm.Var2 = "Test" Sub TestForm() Dim frm As UserForm1 Set frm = New UserForm1 Load frm frm.Var1 = 42 frm.Var2 = "Test" frm.Show Unload frm End Sub Scarica frm Sub TestForm() Dim frm As UserForm1 Set frm = New UserForm1 Load frm frm.Var1 = 42 frm.Var2 = "Test" frm.Show Unload frm End Sub 

In questo modo puoi passare le variables a una forma senza utilizzare variables globali.

Ecco una risposta ampiamente accettata su Scope Variabili. https://stackoverflow.com/a/3815797/3961708

Se hai decalciato la tua variabile all'interno di questo lavoro, devi accedervi qualificandolo. Come ThisWorkbook.VariableName

Ma con UserForms consiglio di utilizzare Proprietà per il stream di dati. Quello è il modo pulito e robusto di farlo. Ottieni l'abitudine di utilizzare le properties; e lo trovenetworking molto vantaggioso per i Form utente.

Esempio:

Aggiungi questo codice nel ThisWorkbook

 Option Explicit '/ As this variable is defined in ThisWorkBook, you need to qualify it to access anywher else. '/ Example ThisWorkbook.x Public x As Integer Sub test() Dim uf As New UserForm1 x = 10 '/ Set the propertyvalue uf.TestSquare = 5 '/Show the form uf.Show '/ Get the property value MsgBox "Square is (by property) : " & uf.TestSquare '/Get Variable MsgBox "Square is (by variable) : " & x Unload uf End Sub Opzione esplicita Option Explicit '/ As this variable is defined in ThisWorkBook, you need to qualify it to access anywher else. '/ Example ThisWorkbook.x Public x As Integer Sub test() Dim uf As New UserForm1 x = 10 '/ Set the propertyvalue uf.TestSquare = 5 '/Show the form uf.Show '/ Get the property value MsgBox "Square is (by property) : " & uf.TestSquare '/Get Variable MsgBox "Square is (by variable) : " & x Unload uf End Sub '/ Poiché questa variabile è definita in ThisWorkBook, devi qualificarla per accedere a qualunque altra cosa. Option Explicit '/ As this variable is defined in ThisWorkBook, you need to qualify it to access anywher else. '/ Example ThisWorkbook.x Public x As Integer Sub test() Dim uf As New UserForm1 x = 10 '/ Set the propertyvalue uf.TestSquare = 5 '/Show the form uf.Show '/ Get the property value MsgBox "Square is (by property) : " & uf.TestSquare '/Get Variable MsgBox "Square is (by variable) : " & x Unload uf End Sub '/ Esempio ThisWorkbook.x Option Explicit '/ As this variable is defined in ThisWorkBook, you need to qualify it to access anywher else. '/ Example ThisWorkbook.x Public x As Integer Sub test() Dim uf As New UserForm1 x = 10 '/ Set the propertyvalue uf.TestSquare = 5 '/Show the form uf.Show '/ Get the property value MsgBox "Square is (by property) : " & uf.TestSquare '/Get Variable MsgBox "Square is (by variable) : " & x Unload uf End Sub Pubblico x come integer Option Explicit '/ As this variable is defined in ThisWorkBook, you need to qualify it to access anywher else. '/ Example ThisWorkbook.x Public x As Integer Sub test() Dim uf As New UserForm1 x = 10 '/ Set the propertyvalue uf.TestSquare = 5 '/Show the form uf.Show '/ Get the property value MsgBox "Square is (by property) : " & uf.TestSquare '/Get Variable MsgBox "Square is (by variable) : " & x Unload uf End Sub Dim uf come nuovo UserForm1 Option Explicit '/ As this variable is defined in ThisWorkBook, you need to qualify it to access anywher else. '/ Example ThisWorkbook.x Public x As Integer Sub test() Dim uf As New UserForm1 x = 10 '/ Set the propertyvalue uf.TestSquare = 5 '/Show the form uf.Show '/ Get the property value MsgBox "Square is (by property) : " & uf.TestSquare '/Get Variable MsgBox "Square is (by variable) : " & x Unload uf End Sub x = 10 Option Explicit '/ As this variable is defined in ThisWorkBook, you need to qualify it to access anywher else. '/ Example ThisWorkbook.x Public x As Integer Sub test() Dim uf As New UserForm1 x = 10 '/ Set the propertyvalue uf.TestSquare = 5 '/Show the form uf.Show '/ Get the property value MsgBox "Square is (by property) : " & uf.TestSquare '/Get Variable MsgBox "Square is (by variable) : " & x Unload uf End Sub '/ Imposta la properties; valore Option Explicit '/ As this variable is defined in ThisWorkBook, you need to qualify it to access anywher else. '/ Example ThisWorkbook.x Public x As Integer Sub test() Dim uf As New UserForm1 x = 10 '/ Set the propertyvalue uf.TestSquare = 5 '/Show the form uf.Show '/ Get the property value MsgBox "Square is (by property) : " & uf.TestSquare '/Get Variable MsgBox "Square is (by variable) : " & x Unload uf End Sub uf.TestSquare = 5 Option Explicit '/ As this variable is defined in ThisWorkBook, you need to qualify it to access anywher else. '/ Example ThisWorkbook.x Public x As Integer Sub test() Dim uf As New UserForm1 x = 10 '/ Set the propertyvalue uf.TestSquare = 5 '/Show the form uf.Show '/ Get the property value MsgBox "Square is (by property) : " & uf.TestSquare '/Get Variable MsgBox "Square is (by variable) : " & x Unload uf End Sub '/ Mostra il module Option Explicit '/ As this variable is defined in ThisWorkBook, you need to qualify it to access anywher else. '/ Example ThisWorkbook.x Public x As Integer Sub test() Dim uf As New UserForm1 x = 10 '/ Set the propertyvalue uf.TestSquare = 5 '/Show the form uf.Show '/ Get the property value MsgBox "Square is (by property) : " & uf.TestSquare '/Get Variable MsgBox "Square is (by variable) : " & x Unload uf End Sub '/ Ottieni il valore della properties; Option Explicit '/ As this variable is defined in ThisWorkBook, you need to qualify it to access anywher else. '/ Example ThisWorkbook.x Public x As Integer Sub test() Dim uf As New UserForm1 x = 10 '/ Set the propertyvalue uf.TestSquare = 5 '/Show the form uf.Show '/ Get the property value MsgBox "Square is (by property) : " & uf.TestSquare '/Get Variable MsgBox "Square is (by variable) : " & x Unload uf End Sub MsgBox "Square è (per properties;):" & uf.TestSquare Option Explicit '/ As this variable is defined in ThisWorkBook, you need to qualify it to access anywher else. '/ Example ThisWorkbook.x Public x As Integer Sub test() Dim uf As New UserForm1 x = 10 '/ Set the propertyvalue uf.TestSquare = 5 '/Show the form uf.Show '/ Get the property value MsgBox "Square is (by property) : " & uf.TestSquare '/Get Variable MsgBox "Square is (by variable) : " & x Unload uf End Sub '/ Get Variable Option Explicit '/ As this variable is defined in ThisWorkBook, you need to qualify it to access anywher else. '/ Example ThisWorkbook.x Public x As Integer Sub test() Dim uf As New UserForm1 x = 10 '/ Set the propertyvalue uf.TestSquare = 5 '/Show the form uf.Show '/ Get the property value MsgBox "Square is (by property) : " & uf.TestSquare '/Get Variable MsgBox "Square is (by variable) : " & x Unload uf End Sub MsgBox "è (per variabile):" & x Option Explicit '/ As this variable is defined in ThisWorkBook, you need to qualify it to access anywher else. '/ Example ThisWorkbook.x Public x As Integer Sub test() Dim uf As New UserForm1 x = 10 '/ Set the propertyvalue uf.TestSquare = 5 '/Show the form uf.Show '/ Get the property value MsgBox "Square is (by property) : " & uf.TestSquare '/Get Variable MsgBox "Square is (by variable) : " & x Unload uf End Sub Scaricare uf Option Explicit '/ As this variable is defined in ThisWorkBook, you need to qualify it to access anywher else. '/ Example ThisWorkbook.x Public x As Integer Sub test() Dim uf As New UserForm1 x = 10 '/ Set the propertyvalue uf.TestSquare = 5 '/Show the form uf.Show '/ Get the property value MsgBox "Square is (by property) : " & uf.TestSquare '/Get Variable MsgBox "Square is (by variable) : " & x Unload uf End Sub 

Adesso aggiungi un Form utente, chiamato UserForm1 e aggiungi questo codice

 Option Explicit Private m_lTestSquare As Long Public Property Get TestSquare() As Long TestSquare = m_lTestSquare End Property Public Property Let TestSquare(ByVal lNewValue As Long) m_lTestSquare = lNewValue End Property Private Sub UserForm_Click() '/ Accessing the Variable Defined inside ThisWorkkbook ThisWorkbook.x = ThisWorkbook.x * ThisWorkbook.x '/ Changing Property Value Me.TestSquare = Me.TestSquare * Me.TestSquare Me.Hide End Sub Opzione esplicita Option Explicit Private m_lTestSquare As Long Public Property Get TestSquare() As Long TestSquare = m_lTestSquare End Property Public Property Let TestSquare(ByVal lNewValue As Long) m_lTestSquare = lNewValue End Property Private Sub UserForm_Click() '/ Accessing the Variable Defined inside ThisWorkkbook ThisWorkbook.x = ThisWorkbook.x * ThisWorkbook.x '/ Changing Property Value Me.TestSquare = Me.TestSquare * Me.TestSquare Me.Hide End Sub Privato m_lTestSquare As Long Option Explicit Private m_lTestSquare As Long Public Property Get TestSquare() As Long TestSquare = m_lTestSquare End Property Public Property Let TestSquare(ByVal lNewValue As Long) m_lTestSquare = lNewValue End Property Private Sub UserForm_Click() '/ Accessing the Variable Defined inside ThisWorkkbook ThisWorkbook.x = ThisWorkbook.x * ThisWorkbook.x '/ Changing Property Value Me.TestSquare = Me.TestSquare * Me.TestSquare Me.Hide End Sub Proprietà pubblica Get TestSquare () As Long Option Explicit Private m_lTestSquare As Long Public Property Get TestSquare() As Long TestSquare = m_lTestSquare End Property Public Property Let TestSquare(ByVal lNewValue As Long) m_lTestSquare = lNewValue End Property Private Sub UserForm_Click() '/ Accessing the Variable Defined inside ThisWorkkbook ThisWorkbook.x = ThisWorkbook.x * ThisWorkbook.x '/ Changing Property Value Me.TestSquare = Me.TestSquare * Me.TestSquare Me.Hide End Sub TestSquare = m_lTestSquare Option Explicit Private m_lTestSquare As Long Public Property Get TestSquare() As Long TestSquare = m_lTestSquare End Property Public Property Let TestSquare(ByVal lNewValue As Long) m_lTestSquare = lNewValue End Property Private Sub UserForm_Click() '/ Accessing the Variable Defined inside ThisWorkkbook ThisWorkbook.x = ThisWorkbook.x * ThisWorkbook.x '/ Changing Property Value Me.TestSquare = Me.TestSquare * Me.TestSquare Me.Hide End Sub Proprietà pubblica Lasciate TestSquare (ByVal lNewValue quanto a lungo) Option Explicit Private m_lTestSquare As Long Public Property Get TestSquare() As Long TestSquare = m_lTestSquare End Property Public Property Let TestSquare(ByVal lNewValue As Long) m_lTestSquare = lNewValue End Property Private Sub UserForm_Click() '/ Accessing the Variable Defined inside ThisWorkkbook ThisWorkbook.x = ThisWorkbook.x * ThisWorkbook.x '/ Changing Property Value Me.TestSquare = Me.TestSquare * Me.TestSquare Me.Hide End Sub m_lTestSquare = lNewValue Option Explicit Private m_lTestSquare As Long Public Property Get TestSquare() As Long TestSquare = m_lTestSquare End Property Public Property Let TestSquare(ByVal lNewValue As Long) m_lTestSquare = lNewValue End Property Private Sub UserForm_Click() '/ Accessing the Variable Defined inside ThisWorkkbook ThisWorkbook.x = ThisWorkbook.x * ThisWorkbook.x '/ Changing Property Value Me.TestSquare = Me.TestSquare * Me.TestSquare Me.Hide End Sub '/ Accesso alla variabile definita all'interno di ThisWorkkbook Option Explicit Private m_lTestSquare As Long Public Property Get TestSquare() As Long TestSquare = m_lTestSquare End Property Public Property Let TestSquare(ByVal lNewValue As Long) m_lTestSquare = lNewValue End Property Private Sub UserForm_Click() '/ Accessing the Variable Defined inside ThisWorkkbook ThisWorkbook.x = ThisWorkbook.x * ThisWorkbook.x '/ Changing Property Value Me.TestSquare = Me.TestSquare * Me.TestSquare Me.Hide End Sub QuestoWorkbook.x = ThisWorkbook.x * ThisWorkbook.x Option Explicit Private m_lTestSquare As Long Public Property Get TestSquare() As Long TestSquare = m_lTestSquare End Property Public Property Let TestSquare(ByVal lNewValue As Long) m_lTestSquare = lNewValue End Property Private Sub UserForm_Click() '/ Accessing the Variable Defined inside ThisWorkkbook ThisWorkbook.x = ThisWorkbook.x * ThisWorkbook.x '/ Changing Property Value Me.TestSquare = Me.TestSquare * Me.TestSquare Me.Hide End Sub '/ Modifica del valore delle properties; Option Explicit Private m_lTestSquare As Long Public Property Get TestSquare() As Long TestSquare = m_lTestSquare End Property Public Property Let TestSquare(ByVal lNewValue As Long) m_lTestSquare = lNewValue End Property Private Sub UserForm_Click() '/ Accessing the Variable Defined inside ThisWorkkbook ThisWorkbook.x = ThisWorkbook.x * ThisWorkbook.x '/ Changing Property Value Me.TestSquare = Me.TestSquare * Me.TestSquare Me.Hide End Sub Me.TestSquare = Me.TestSquare * Me.TestSquare Option Explicit Private m_lTestSquare As Long Public Property Get TestSquare() As Long TestSquare = m_lTestSquare End Property Public Property Let TestSquare(ByVal lNewValue As Long) m_lTestSquare = lNewValue End Property Private Sub UserForm_Click() '/ Accessing the Variable Defined inside ThisWorkkbook ThisWorkbook.x = ThisWorkbook.x * ThisWorkbook.x '/ Changing Property Value Me.TestSquare = Me.TestSquare * Me.TestSquare Me.Hide End Sub 

Ora quando esegui la sottosezione Test da ThisWorkbook vedrai come puoi accedere a variables e properties; in tutto il codice.