UserForm to Save

[sowetoddid]

I have created a workbook that was seriously manipulated by the users. As a result, I am trying to create a stronger version with full protection and limited personal options. One of those is the ability to choose a file name for saving.

I have started to create a userform that I would like to tie to a command button ("save") on a sheet labeled "Menu". The userform has the following boxes/buttons...

1. Text Box 1 - for user to freely input their name
2. Combo Box 1 - Lists available areas (Alabama, Texas, New Jersey, Oklahoma, Michigan)
3. Combo Box 2 - Lists each month of the year
4. Combo Box 3 - Lists years 2005 through 2010
5. Command Button 1 - "Browse" will open a window for the user to select the path that the file will be saved under. The path will then be displayed in a box to the right of the button
6. Command Button 2 - Saves the file as with the corresponding inputs, under the chosen file path....

i.e. ChosenFilePath\TB1_CB1_CB2_CB3


How would I go about doing this? The combo boxes and other functions are confusing me when I get into trying a userform.

As a related question, what code is used to disable the ability to save, saveas, etc., unless it is done via the userform?


Thanks for any help you can provide.

[sowetoddid]

I saw this format in a web search. Is this the way to add combobox values in a userform?


Private Sub UserForm_Initialize()
With ComboBox1
.AddItem "Texas"
.AddItem "New Jersey"
With ComboBox2
.AddItem "January"
.AddItem "February"
.AddItem "March"
.AddItem "April"
.AddItem "May"
.AddItem "June"
.AddItem "July"
.AddItem "August"
.AddItem "September"
.AddItem "October"
.AddItem "November"
.AddItem "December"
With ComboBox3
.AddItem "2005"
.AddItem "2006"
.AddItem "2007"
.AddItem "2008"
.AddItem "2009"
.AddItem "2010"

[sowetoddid]

For those interested, this solved part of my problem. I pieced together pieces from some of the code theDude gave me in a different post.

This code creates variables for each user input on the "Save" form. Then, when they click the save button, the form is saved with those inputs.

Private Sub CommandButton2_Click()

Dim N, A, M, Y
N = TextBox1.Value
A = Trim(OpArea.Value)
M = Trim(Month.Value)
Y = Trim(Year.Value)
ActiveWorkbook.SaveAs N & A & M & Y

End Sub

This code fills in the possible data selections for each combobox, giving the user a list of choices to select. At the end of the code, a label on the form displays the default path where the file will be saved (from DominicB post)

Private Sub UserForm_Activate()

With OpArea

.AddItem "AnadarkoOK"
.AddItem "AnadarkoTX"
.AddItem "ETXSouth"
.AddItem "ETXNorth"
.AddItem "Gloria"
.AddItem "NTXEast"
.AddItem "NTXWest"
.AddItem "MidLa"
.AddItem "Mississippi"

With Month

.AddItem "January"
.AddItem "February"
.AddItem "March"
.AddItem "April"
.AddItem "May"
.AddItem "June"
.AddItem "July"
.AddItem "August"
.AddItem "September"
.AddItem "October"
.AddItem "November"
.AddItem "December"

With Year

.AddItem "2005"
.AddItem "2006"
.AddItem "2007"
.AddItem "2008"
.AddItem "2009"
.AddItem "2010"
.AddItem "2011"
.AddItem "2012"
.AddItem "2013"
.AddItem "2014"
.AddItem "2015"
.AddItem "2016"
.AddItem "2017"
.AddItem "2018"
.AddItem "2019"
.AddItem "2020"

End With
End With
End With
SaveLocationLabel.Caption = Application.DefaultFilePath

End Sub