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?
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.