Unprotect from Forms Listbox

[ma_hutchins]

Using Excel 2000, I have a simple macro to hide or show a column
depending on the choice made from a list box (forms) control on the
worksheet:

Sub ModelSelect_Change()
On Error Resume Next
ActiveCell.Activate
ActiveSheet.Unprotect
If Range("ModelSelect").Value = 1 Then
'Hide it
Columns("E:E").Select
Selection.EntireColumn.Hidden = True
Else
'Show it
Columns("D:F").Select
Selection.EntireColumn.Hidden = False
End If
ActiveSheet.Protect
End Sub

However, the unprotect fails and Excel tells me "the cell or chart you
are trying to change is protected....", etc. and tells me to remove the
protection which is, of course, what the macro is attempting to do!
Where am I going wrong?
Cheers......Mike

 

[JulieD]

Hi

i get the same error message if the cell linked to the listbox is
protected - so unprotect the sheet first, choose this linked cell, choose
format / cells / protection, untick locked, then your code should work fine
(BTW not sure what activecell.activate actually achieves).

Cheers
JulieD

 

[Harald Staff]

(BTW not sure what activecell.activate actually achieves).

Hi Julie

It's generally useful for Excel 97 where ActiveX controls on worksheets tend
to take focus, resulting in no selection, no active cell and a few less
logical side effects. (Not saying that one should write 97 compatible code
as a rule, but activecell.activate doesn't limit or harm anything.)

Best wishes Harald

 

[JulieD]

Hi Harald

thanks for the clarification.

Cheers
JulieD

 

[ma_hutchins]

Thanks a lot, Julie, it works fine now. Something that simple has
caused me so much grief today!
Cheers....Mike

 

[ma_hutchins]

Thanks a lot, Julie, it works fine now. That little problem caused me
so much grief today!
Cheers....Mike