Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unprotect from Forms Listbox
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unprotect from Forms Listbox
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 wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unprotect from Forms Listbox
"JulieD" skrev i melding
... (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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unprotect from Forms Listbox
Hi Harald
thanks for the clarification. Cheers JulieD "Harald Staff" wrote in message ... "JulieD" skrev i melding ... (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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unprotect from Forms Listbox
Thanks a lot, Julie, it works fine now. Something that simple has
caused me so much grief today! Cheers....Mike |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unprotect from Forms Listbox
Thanks a lot, Julie, it works fine now. That little problem caused me
so much grief today! Cheers....Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MS Forms ListBox no longer accessible??? | Excel Programming | |||
MultiSelect Listbox (Forms Toolbar) | Excel Programming | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming | |||
Is refreshing listbox rowsource in listbox click event possible? | Excel Programming | |||
Strange (forms) ListBox behavior | Excel Programming |