Userform Personal Workbook - Runtime Dynamic Population - Run Code Module Macro

[dj44]

Folks good day ,
:)

This has got me a bit tangled and I can't seem to work out that logic I will try to be as precise as possible.

In my PERSONAL.XLSB I have a userform named UF2

I am now trying to run this from my CURRENT ACTIVE workbook.

In my Active workbook sheet1 code module I have put two macros
Macro 1
Macro 2

The goal is to
At runtime dynamically populate userform stored in the personal workbook
And run these 2 sheet code macros via the command buttons.

The reason I want to do this, so that I don't have so many user forms because it's a job and a half and I keep losing them so I just want 1 basic user form in my personal workbook and then try and run it from whatever active workbook I'm in.

I hope this makes some sort of sense
This one's for the pros because I know it's something to do with public variable declaration

But that's where I got stuck and as far as I could go


I'm sure there are other ways to make it at run time but - i store code in my sheets and i designed a nice userform and would like to use the one in the personal.xlsb



Hope it's not too much of a complex task your expertise is appreciated

[Paul_Hossler]

In your not-Personal



Option Explicit

Sub ShowUF2()

Application.Run ("Personal.xlsm!ShowPersonalUserform")
End Sub




In your Personal in a standard module



Option Explicit

Sub ShowPersonalUserform()
Load UF2
UF2.Show
End Sub




I put this in the UF Initialize in the userform in your Personal




Option Explicit

Private Sub UserForm_Initialize()
Me.CommandButton1.Caption = "Hello Button 1"
Me.CommandButton2.Caption = "Button 2"
End Sub




Passing information from your not-Personal is a little harder

[dj44]

Hello Paul,

thanks for the layout of the system.:)

i have a hunch about the userform calling it in my active workbook.

but my linguisitc skills cant elaborate on what i mean.

I often see run macro from active workbook

Its kind of similar to the input box where im passing a variable.

But im calling it from my active workbook to the personal.xlsb

Im good at making things really diffcult becuase i have a simplistiv view of things.

may be i need a getobject of excel

let me chew on this . . .

[SamT]

Paul said:
In your Personal in a standard module


Option Explicit

Sub ShowPersonalUserform()
Load UF2
UF2.Show
End Sub



Try

Public Sub ShowPersonalUserform()
Load UF2
UF2.Show
End Sub

[Paul_Hossler]

Hello Paul,

thanks for the layout of the system.:)

i have a hunch about the userform calling it in my active workbook.

but my linguisitc skills cant elaborate on what i mean.

I often see run macro from active workbook

Its kind of similar to the input box where im passing a variable.

But im calling it from my active workbook to the personal.xlsb

Im good at making things really diffcult becuase i have a simplistiv view of things.

may be i need a getobject of excel

let me chew on this . . .


Didn't follow a lot of that, but with the macro ShowPersonalUserform in the regular (i.e. not PERSONAL.XLSM) Run Macro or Alt-F8 will see it and you can run it

[dj44]

Update on my useform project.

after spending a couple of days trying to tame this well I decided to keep the workbook within the same workbook.

And this is a solution this is what I did



Userform Resides Within the Workbook

1 Template Userform
Put My Macros and Captions to populate the Userform within each private worksheet


1. In a Standard Module



Sub Show_UserForm1()

' When the Userform is loaded -
' Populates the Button Names from the Active Worksheet Code Module where i put the information for this particualar userform


Dim oCaptionSheet As String 'The Captions is Stored in a Private Worksheet Code module

oCaptionSheet = "Sheet" & ActiveSheet.Index & "." & "CaptionsSheet"

Application.Run oCaptionSheet


UserForm1.Show

End Sub




2. UserForm Code



Private Sub CommandButton1_Click()


Dim oMacroSheet As String 'The Macro is Stored in a Private Worksheet Code module

oMacroSheet = "Sheet" & ActiveSheet.Index & "." & "Macro1"

Application.Run oMacroSheet

End Sub


Private Sub CommandButton2_Click()

Dim oMacroSheet As String 'The Macro is Stored in a Private Worksheet Code module

oMacroSheet = "Sheet" & ActiveSheet.Index & "." & "Macro2"

Application.Run oMacroSheet

End Sub

etc



3. In Private Worksheet - Code Module



Macros

Private Sub Macro1()

Your macro code

End Sub

Private Sub Macro2()

Your macro code

End Sub


Captions to Populate the userform


Private Sub CaptionsSheet()

Userform1.CommandButton1.Caption = " Task 1 "
Userform1.CommandButton2.Caption = " Task 2 "

End Sub






I just wanted to use one basic userform and then put my code in each worksheet code module to keep it separate and organised

so I don't have to have that many user forms as I accidently delete them and I'll lose them and then it's like oh :doh:


I noticed one issue when i rename the Sheet Index to a new number i have a feeling it retains the old number or something fishy going on

I was able to work out after much struggle how to pass the command button variable from the private worksheet module to the userform code :)