Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identifying toolbars.
I have wriiten a few lines of code that cause Excel to switch to full screen
and to eliminate the toolbars and formula bar etc when the sheet is activated .. On deactivation I also have a macro to restore the screen back to normal view along with the toolbars that I normally use. My question is: If I am applying this code on another person's computer how can I determine what toolbars were visible prior to the sheet activation macro so that I can restore them on de-activation. Thank you |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identifying toolbars.
Store the toolbars on a hidden worksheet as the application opens and then
restore them as the application closes. The following code stores toolbars in sheet codename shTBsheet. Call this code from the open workbook event. The next sub restores the toolbars based on those previously saved, call this code from the before close workbook event. You can use a similar process to record the current options, if they need to be changed. Sub HideAllToolBars() Dim TB As CommandBar Dim TBNum As Integer ' record the toolbars visible in excel on load ' then disable ALL toolbars shTBsheet.Cells.Clear TBNum = 0 For Each TB In CommandBars If TB.Type = msoBarTypeNormal Then If TB.Visible Then TBNum = TBNum + 1 TB.Visible = False shTBsheet.Cells(TBNum, 1) = TB.Name End If TB.Enabled = False End If Next TB End Sub Sub RestoreToolBars() Dim xlr As Integer, xr As Integer Application.ScreenUpdating = False With shTBsheet xlr = .Cells(Rows.Count, 1).End(xlUp).Row For xr = 1 To xlr CommandBars(.Cells(xr, 1).Value).Enabled = True CommandBars(.Cells(xr, 1).Value).Visible = True Next xr End With End Sub -- Cheers Nigel "Ben" wrote in message ... I have wriiten a few lines of code that cause Excel to switch to full screen and to eliminate the toolbars and formula bar etc when the sheet is activated . On deactivation I also have a macro to restore the screen back to normal view along with the toolbars that I normally use. My question is: If I am applying this code on another person's computer how can I determine what toolbars were visible prior to the sheet activation macro so that I can restore them on de-activation. Thank you |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identifying toolbars.
Private mFormulaBar
Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim oCB As CommandBar For Each oCB In Application.CommandBars oCB.Enabled = True Next oCB Application.DisplayFormulaBar = mFormulaBar End Sub Private Sub Workbook_Open() Dim oCB As CommandBar For Each oCB In Application.CommandBars oCB.Enabled = False Next oCB mFormulaBar = Application.DisplayFormulaBar Application.DisplayFormulaBar = False End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Ben" wrote in message ... I have wriiten a few lines of code that cause Excel to switch to full screen and to eliminate the toolbars and formula bar etc when the sheet is activated . On deactivation I also have a macro to restore the screen back to normal view along with the toolbars that I normally use. My question is: If I am applying this code on another person's computer how can I determine what toolbars were visible prior to the sheet activation macro so that I can restore them on de-activation. Thank you |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identifying toolbars.
I use this code in a program I have and it takes away then restores the
peripheral controls without changing any settings. To hide: With ActiveWindow .DisplayHorizontalScrollBar = False 'Make space and clear screen .DisplayVerticalScrollBar = False .DisplayWorkbookTabs = False .DisplayHeadings = False .DisplayGridlines = False End With To resto With ActiveWindow 'This with statement sets everything to normal .DisplayHorizontalScrollBar = True .DisplayVerticalScrollBar = True .DisplayWorkbookTabs = True .DisplayHeadings = True .DisplayGridlines = True End With "Ben" wrote: I have wriiten a few lines of code that cause Excel to switch to full screen and to eliminate the toolbars and formula bar etc when the sheet is activated . On deactivation I also have a macro to restore the screen back to normal view along with the toolbars that I normally use. My question is: If I am applying this code on another person's computer how can I determine what toolbars were visible prior to the sheet activation macro so that I can restore them on de-activation. Thank you |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identifying toolbars.
without changing any settings
that would be true if they had all those peripheral controls set to true to start with, but that isn't always the case. Think you would need to store the current settings and then restore them. -- Regards, Tom Ogilvy "JLGWhiz" wrote in message ... I use this code in a program I have and it takes away then restores the peripheral controls without changing any settings. To hide: With ActiveWindow .DisplayHorizontalScrollBar = False 'Make space and clear screen .DisplayVerticalScrollBar = False .DisplayWorkbookTabs = False .DisplayHeadings = False .DisplayGridlines = False End With To resto With ActiveWindow 'This with statement sets everything to normal .DisplayHorizontalScrollBar = True .DisplayVerticalScrollBar = True .DisplayWorkbookTabs = True .DisplayHeadings = True .DisplayGridlines = True End With "Ben" wrote: I have wriiten a few lines of code that cause Excel to switch to full screen and to eliminate the toolbars and formula bar etc when the sheet is activated . On deactivation I also have a macro to restore the screen back to normal view along with the toolbars that I normally use. My question is: If I am applying this code on another person's computer how can I determine what toolbars were visible prior to the sheet activation macro so that I can restore them on de-activation. Thank you |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identifying toolbars.
Hi Nigel
I ran this code and the problem is that the toolbars that are available after running the restore macro are limited only to the ones that were initially selected (i.e. all the other toolbars such as control toolbox, drawing are no longer shown) and I can't find a way of getting them back in the view/toolbars menu (!) Please help. Cheers Rob "Nigel" wrote: Store the toolbars on a hidden worksheet as the application opens and then restore them as the application closes. The following code stores toolbars in sheet codename shTBsheet. Call this code from the open workbook event. The next sub restores the toolbars based on those previously saved, call this code from the before close workbook event. You can use a similar process to record the current options, if they need to be changed. Sub HideAllToolBars() Dim TB As CommandBar Dim TBNum As Integer ' record the toolbars visible in excel on load ' then disable ALL toolbars shTBsheet.Cells.Clear TBNum = 0 For Each TB In CommandBars If TB.Type = msoBarTypeNormal Then If TB.Visible Then TBNum = TBNum + 1 TB.Visible = False shTBsheet.Cells(TBNum, 1) = TB.Name End If TB.Enabled = False End If Next TB End Sub Sub RestoreToolBars() Dim xlr As Integer, xr As Integer Application.ScreenUpdating = False With shTBsheet xlr = .Cells(Rows.Count, 1).End(xlUp).Row For xr = 1 To xlr CommandBars(.Cells(xr, 1).Value).Enabled = True CommandBars(.Cells(xr, 1).Value).Visible = True Next xr End With End Sub -- Cheers Nigel "Ben" wrote in message ... I have wriiten a few lines of code that cause Excel to switch to full screen and to eliminate the toolbars and formula bar etc when the sheet is activated . On deactivation I also have a macro to restore the screen back to normal view along with the toolbars that I normally use. My question is: If I am applying this code on another person's computer how can I determine what toolbars were visible prior to the sheet activation macro so that I can restore them on de-activation. Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Identifying | Excel Discussion (Misc queries) | |||
Identifying Visible Toolbars | Excel Programming | |||
Please Help, No Toolbars in my view-toolbars! | Excel Programming | |||
Identifying Top row | Excel Programming | |||
Identifying a value | Excel Programming |