Excel 97 to XP problem
I have some code that works absolutely fines in Excel 97
but is giving "odd" behaviour in XP. It's to do with the right click menu on the worksheet tabs - I'm trying to selectively disable certain aspects (e.g. view code). What used to work is: Application.CommandBars(27).Controls("view code").Enabled = False I've tried to work out how to get this menu control using: For Each ctl In Application.CommandBars("workbook tabs").Controls Debug.Print ctl.Caption Next But this returns the names of each of the worksheets. If I do something like: Application.CommandBars("workbook tabs").ShowPopup I get a menu with the three worksheet names as a menu. Hmm Very confused. Anyone know how I can set these controls to be enabled? Thanks Leyton ps I'd like to keep this working in both Excel 97 and 2002! |
Excel 97 to XP problem
Hi Leyton
27 is a index number and not the number of the Ply bar Application.CommandBars("Ply").Controls("view code").Enabled = False Or better use this (work also in a Dutch version for example) Application.CommandBars("Ply").FindControl(ID:=156 1).Enabled = False -- Regards Ron de Bruin http://www.rondebruin.nl "Leyton" wrote in message ... I have some code that works absolutely fines in Excel 97 but is giving "odd" behaviour in XP. It's to do with the right click menu on the worksheet tabs - I'm trying to selectively disable certain aspects (e.g. view code). What used to work is: Application.CommandBars(27).Controls("view code").Enabled = False I've tried to work out how to get this menu control using: For Each ctl In Application.CommandBars("workbook tabs").Controls Debug.Print ctl.Caption Next But this returns the names of each of the worksheets. If I do something like: Application.CommandBars("workbook tabs").ShowPopup I get a menu with the three worksheet names as a menu. Hmm Very confused. Anyone know how I can set these controls to be enabled? Thanks Leyton ps I'd like to keep this working in both Excel 97 and 2002! |
Excel 97 to XP problem
Thanks Ron,
One further question though is has the index changed since '97. the reason I ask is that now that you mention it I did (once) know that this is the PLY bar (it was a while ago I coded this originally!!) but I kept having trouble referring to it directly hence using the index. Cheers Leyton -----Original Message----- Hi Leyton 27 is a index number and not the number of the Ply bar Application.CommandBars("Ply").Controls("view code").Enabled = False Or better use this (work also in a Dutch version for example) Application.CommandBars("Ply").FindControl (ID:=1561).Enabled = False -- Regards Ron de Bruin http://www.rondebruin.nl "Leyton" wrote in message ... I have some code that works absolutely fines in Excel 97 but is giving "odd" behaviour in XP. It's to do with the right click menu on the worksheet tabs - I'm trying to selectively disable certain aspects (e.g. view code). What used to work is: Application.CommandBars(27).Controls("view code").Enabled = False I've tried to work out how to get this menu control using: For Each ctl In Application.CommandBars("workbook tabs").Controls Debug.Print ctl.Caption Next But this returns the names of each of the worksheets. If I do something like: Application.CommandBars("workbook tabs").ShowPopup I get a menu with the three worksheet names as a menu. Hmm Very confused. Anyone know how I can set these controls to be enabled? Thanks Leyton ps I'd like to keep this working in both Excel 97 and 2002! . |
Excel 97 to XP problem
Hi Leyton
One further question though is has the index changed Yes Never us the Index because a user can add a commandbar also. If you run this on a empty sheet you get a list Sub Get_Commandbars_Info() Dim CBar As CommandBar Dim NewWS As Worksheet Dim RNum As Long RNum = 1 Set NewWS = Worksheets.Add For Each CBar In Application.CommandBars NewWS.Cells(RNum, "A").Value = CBar.Name NewWS.Cells(RNum, "B").Value = CBar.NameLocal NewWS.Cells(RNum, "C").Value = CBar.Index RNum = RNum + 1 Next CBar NewWS.Columns.AutoFit End Sub Use always the English name of the commandbar and use findcontrol or the local name for the controls. More info you can find here http://www.rondebruin.com/menuid.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Leyton" wrote in message ... Thanks Ron, One further question though is has the index changed since '97. the reason I ask is that now that you mention it I did (once) know that this is the PLY bar (it was a while ago I coded this originally!!) but I kept having trouble referring to it directly hence using the index. Cheers Leyton -----Original Message----- Hi Leyton 27 is a index number and not the number of the Ply bar Application.CommandBars("Ply").Controls("view code").Enabled = False Or better use this (work also in a Dutch version for example) Application.CommandBars("Ply").FindControl (ID:=1561).Enabled = False -- Regards Ron de Bruin http://www.rondebruin.nl "Leyton" wrote in message ... I have some code that works absolutely fines in Excel 97 but is giving "odd" behaviour in XP. It's to do with the right click menu on the worksheet tabs - I'm trying to selectively disable certain aspects (e.g. view code). What used to work is: Application.CommandBars(27).Controls("view code").Enabled = False I've tried to work out how to get this menu control using: For Each ctl In Application.CommandBars("workbook tabs").Controls Debug.Print ctl.Caption Next But this returns the names of each of the worksheets. If I do something like: Application.CommandBars("workbook tabs").ShowPopup I get a menu with the three worksheet names as a menu. Hmm Very confused. Anyone know how I can set these controls to be enabled? Thanks Leyton ps I'd like to keep this working in both Excel 97 and 2002! . |
All times are GMT +1. The time now is 06:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com