Using Excel VBA, How Do I Prevent a ComboBox Selection from being edited when the worksheet is protected?

I have a ComboBox ("Model_Select") that allows the user to select the model they want to use when running a VBA macro.  The selection is linked to a cell ("B17").  After the macro runs, I lock cell "B17" to prevent the user from changing the model after the code is run.  This, however, does not prevent the user from editing the selection in the ComboBox.  If they edit the contents of the linked ComboBox an error message stating that cell "B17" is locked from editing pops-up on the screen.  That is fine, however, the content of the ComboBox changes anyway.  In other words, cell "B17" and the ComboBox contents no longer match.  I don't want the user to be able to edit the ComboBox selection unless they Unprotect the Worksheet.  I need to be able to do this with VBA code.  Note: The ComboBox is locked but it does not lock the contents.

I am running MS Office 2007 in 2003 compatibility mode.
df_birderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hello_everybodyCommented:
me.Model_Select.enabled = false
0
df_birderAuthor Commented:
I am aware of that option.  I was looking for a solution that would lock the ComboBox only when the worksheet is protected.  I would want the combobox to be enabled when the user unprotects the worksheet.  Thanks.
0
df_birderAuthor Commented:
I solved my own problem.  Since the value in cell "B17" does not change because it is protected, I was able to change the value in the ComboBox back to the value in cell "B17" whenever the user attempts to change the value in the ComboBox.  This, in effect, prevents the user from changing the value in the ComboBox when the linked value in cell "B17" is locked.  Here is the code that I used (in the sheet module):

Private Sub Model_Select_Change()
   If Me.Model_Select.Value <> Me.Range("B17").Value Then
       Me.Model_Select.Value = Me.Range("B17").Value
   End If
End Sub

Thank you to those who tried to help.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.