L
Limey1971
I'm new to VBA programming so please go easy on me.
I have a spreadsheet I've been developing that has multiple comman
buttons, userforms, etc. The problem I have is with one particula
userform that contains two combo boxes and three command buttons
ComboBox1 links to a list of locations, ComboBox2 links to a list o
suppliers. The idea is that you select a location, select the curren
supplier for that location and then click on either "Cancel", "Update
Exit" or "Update & Next". Cancel will close the userform without makin
any changes, Update & Exit will update the sheet and close the use
form, etc.
The whole thing works fine up until the point when I protect th
worksheet, then I start getting run-time error 91 messages when I clic
on either of the update button, all linked cells are formatted to b
unlocked, obviously I'm missing something but I need help figuring ou
what that something is.
The code used in the Update & Exit button is as follows:
Private Sub UpdateSourceAndExit_Click()
Dim Entry1 As String, Entry2 As String
Entry1 = SourceByLocation.LocationComboBox
Entry2 = SourceByLocation.SourceComboBox
Range("N2:N26").Select
Selection.Find(What:=Entry1).Activate
ActiveCell.Offset(0, 1).Select
ActiveCell = Entry2
Sheets("Sheet1").Cells(1, 1).Select
SourceByPlant.Hide
End Sub
Where:
SourceByLocation = UserForm
LocationComboBox = List of available locations
SourceComboBOx = List of available sources
N2:N26 = List of available locations
Basically the above code looks up the value in LocationComboBox i
range N2:N26, and pastes the value in SourceComboBox into the cel
immediately to the right.
The run-time error occurs in the line that starts with Selection.Find
I think I understand why it's doing it (the combobox is a variable?
but I don't know how to fix it!
Please help if you can.
I'm sure my code writing is primative and messy but as I said, I'm ne
to this and besides, it works great so long as the sheet isn'
protected.
TI
I have a spreadsheet I've been developing that has multiple comman
buttons, userforms, etc. The problem I have is with one particula
userform that contains two combo boxes and three command buttons
ComboBox1 links to a list of locations, ComboBox2 links to a list o
suppliers. The idea is that you select a location, select the curren
supplier for that location and then click on either "Cancel", "Update
Exit" or "Update & Next". Cancel will close the userform without makin
any changes, Update & Exit will update the sheet and close the use
form, etc.
The whole thing works fine up until the point when I protect th
worksheet, then I start getting run-time error 91 messages when I clic
on either of the update button, all linked cells are formatted to b
unlocked, obviously I'm missing something but I need help figuring ou
what that something is.
The code used in the Update & Exit button is as follows:
Private Sub UpdateSourceAndExit_Click()
Dim Entry1 As String, Entry2 As String
Entry1 = SourceByLocation.LocationComboBox
Entry2 = SourceByLocation.SourceComboBox
Range("N2:N26").Select
Selection.Find(What:=Entry1).Activate
ActiveCell.Offset(0, 1).Select
ActiveCell = Entry2
Sheets("Sheet1").Cells(1, 1).Select
SourceByPlant.Hide
End Sub
Where:
SourceByLocation = UserForm
LocationComboBox = List of available locations
SourceComboBOx = List of available sources
N2:N26 = List of available locations
Basically the above code looks up the value in LocationComboBox i
range N2:N26, and pastes the value in SourceComboBox into the cel
immediately to the right.
The run-time error occurs in the line that starts with Selection.Find
I think I understand why it's doing it (the combobox is a variable?
but I don't know how to fix it!
Please help if you can.
I'm sure my code writing is primative and messy but as I said, I'm ne
to this and besides, it works great so long as the sheet isn'
protected.
TI