Making a UserForm to modify current variables

**bassinator** · 11-28-2012, 08:28 PM

Good day World,

I would need a good kick in the butt to start me up for user form stuff, I've tried to find a few things but they aren't helping me for what I really want to do and I can't seem to find the right fit.
My Script works fine but I need to manually edit the top level of the code to modify each variables paths or file names or values each time. (More User Friendly, for me & my colleagues)

How can I make a user form from a running click macro to ease the following variables into a small user form?

vPeriod = "P9" ' Changes the Sheet Name at the End (Month Forecast) <-- A simple txt box to modify the Sheet's name
vData = "Sheet1!$A$1:$L$1000" ' Change Line 1000 to new value if more <-- A simple txt box to modify that 1000 number because the rest always stays the same.
vPath = "C:\FilePath\filename.blabla" 'Initial Report Extract File <--a Simple browser code to locate the file
vTemplate = "PivotStyleMedium2" '<-- Drop Down code in order to add some coloring for females :P
vSaveAs = "C:\Desktop\conciliationP9.xlsx" ' I'm open to suggestions , file path and a naming function?
vLastRecon = "S:\FilePath\[BASS8.xlsx]P8" <--a Simple browser code to locate the file.In this case, it's an excel file /sheet

Then I would add the Run/Generate to load the script with the update variables and Close button for whoever uses it ?

Who can help me or provide me with some sample code to play with to get this running from a userform?

Bassinator

**bassinator** · 11-29-2012, 08:15 AM

Here is my user form now that I have the file! Any help is appreciated, as I mentioned in my previous post, I simply need the code in order to change each variable values etc. I have attached an example and have put a browser code that I found but I'm a rookie!

'vPeriod = "P9" ' Changes the Sheet Name at the End (Month Forecast) <-- A simple txt box to modify the Sheet's name
Private Sub txt_Period_Change()

End Sub
'end of vPeriod

'vData = "Sheet1!$A$1:$L$1000" ' Change Line 1000 to new value if more <-- A simple txt box to modify that 1000 number because the rest always stays the same.
Private Sub txt_ReportValue_Change()

End Sub
'end of vData

'vPath = "C:\FilePath\filename.blabla" 'Initial Report Extract File <--a Simple browser code to locate the file
Private Sub txt_ImportRep_Change()

End Sub

Private Sub browse_Imp_Click()

End Sub
'end of vPath

'vTemplate = "PivotStyleMedium2" '<-- Drop Down code in order to add some coloring for females :P
Private Sub txt_listTemplate_Click()

End Sub
'end of vTemplate

'vLastRecon = "S:\FilePath\[BASS8.xlsx]P8" <--a Simple browser code to locate the file.In this case, it's an excel file /sheet
Private Sub txt_PreviousReport_Change()

End Sub
Private Sub browse_Previous_Click()

End Sub
'end of vLastRecon

'vSaveAs = "C:\Desktop\conciliationP9.xlsx" ' I'm open to suggestions , file path and a naming function?
Private Sub txt_SaveAs_Change()

End Sub

Private Sub browse_SaveAs_Click()

End Sub
'end of vSaveAs

'Run button to run the macro.
Private Sub button_Run_Click()

End Sub

'Exit :)
Private Sub button_Exit_Click()
Unload Me

End Sub