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

Whe
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

--
Message posted from http://www.ExcelForum.com

bump,

any suggestion?


---
Message posted from http://www.ExcelForum.com/

Hi
Before a Select, you normally need to activate the sheet that contains
the range you are selecting from. So you need to put in

worksheets("Whatever").Activate

before the select. A better idea is not to select at all and replace
this

worksheets("Whatever").Activate
Range("N2:N26").Select
Selection.Find(What:=Entry1).Activate
ActiveCell.Offset(0, 1).Select
ActiveCell = Entry2

with this

worksheets("Whatever").Range("N2:N26").Find(What:= Entry1).Activate
ActiveCell.Offset(0, 1).Value = Entry2

I can't see why a password protected sheet would cause a problem. This
may be a coincidence?

regards
Paul

Limey1971 wrote in message ...
I'm new to VBA programming so please go easy on me.

I have a spreadsheet I've been developing that has multiple command
buttons, userforms, etc. The problem I have is with one particular
userform that contains two combo boxes and three command buttons.
ComboBox1 links to a list of locations, ComboBox2 links to a list of
suppliers. The idea is that you select a location, select the current
supplier for that location and then click on either "Cancel", "Update &
Exit" or "Update & Next". Cancel will close the userform without making
any changes, Update & Exit will update the sheet and close the user
form, etc.

The whole thing works fine up until the point when I protect the
worksheet, then I start getting run-time error 91 messages when I click
on either of the update button, all linked cells are formatted to be
unlocked, obviously I'm missing something but I need help figuring out
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

Whe
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 in
range N2:N26, and pastes the value in SourceComboBox into the cell
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 new
to this and besides, it works great so long as the sheet isn't
protected.

TIA


---
Message posted from http://www.ExcelForum.com/