shirley999
01-01-2008, 08:49 AM
See vbaexpress.com/kb/getarticle.php?kb_id=921#instr (sorry I can't post links yet!)
I implemented a variation of lucas' code on my own PC and have been using it very successfully for 3-4 weeks. I just passed it on to a colleague who is distant from me - indeed this is why it was required, easier than trying to talk him through building the toolbars (2 with 26 buttons between them). It worked ok on his laptop but when he put it on his PC he said: -
The downloaded Master seems completely dead. I cannot open anything file, view, format tools, or indeed change pages. ... I left the Master open for half an hour just in case, but still no luck. ... It seemed like the macro was taking a million years to run, yes, and also when I shut it down it seemed to try and load another sheet below for a second.
My 2 new toolbars are positioned at the top so I think '... load another sheet below ...' might be the effect of the sheet moving up screen as they are deleted on close.
Something in the discussion I have puzzled over too. Lucas says it is not necessary to delete the toolbars as they are temporary, and yet there is code in there to delete on close. (I understand why it is necessary to delete at the beginning in case the toolbar already exists.)
My code follows: -
Sub AddNewToolBar()
' This procedure creates a new temporary toolbar.
Dim COMBAR As CommandBar, COMBARCONTRL As CommandBarControl
Dim NETWORK, MACRONAME As String
Application.ScreenUpdating = False
On Error GoTo ErrorHandler
' Create a new toolbar and make it visible.
On Error Resume Next
'Delete the toolbar if it already exists
CommandBars("Network processing").Delete
Set COMBAR = CommandBars.Add(Name:="Network processing", Position:= _
msoBarTop, Temporary:=True)
COMBAR.Visible = True
' Create a button with text on the bar and set some properties.
Sheets("lookup IDs").Select
Range("N1").Activate
Do While ActiveCell.Value <> ""
NETWORK = ActiveCell.Value
MACRONAME = ActiveCell.Offset(0, 1).Value
Set COMBARCONTRL = COMBAR.Controls.Add(Type:=msoControlButton)
With COMBARCONTRL
.Caption = NETWORK
.Style = msoButtonCaption
.TooltipText = "Run " & MACRONAME
'the onaction line tells the button to run a certain macro
.OnAction = "PERSONAL.XLS!" & MACRONAME
.BeginGroup = True
End With
ActiveCell.Offset(1, 0).Select
Loop
'Create another toolbar, as above
CommandBars("TradeDoublerTemp").Delete
Set COMBAR = CommandBars.Add(Name:="TradeDoublerTemp", Position:= _
msoBarTop, Temporary:=False)
COMBAR.Visible = True
Sheets("lookup IDs").Select
Range("Q1").Activate
Do While ActiveCell.Value <> ""
NETWORK = ActiveCell.Value
MACRONAME = ActiveCell.Offset(0, 1).Value
Set COMBARCONTRL = COMBAR.Controls.Add(Type:=msoControlButton)
With COMBARCONTRL
.Caption = NETWORK
.Style = msoButtonCaption
.TooltipText = "Run " & MACRONAME
'the onaction line tells the button to run a certain macro
.OnAction = "PERSONAL.XLS!" & MACRONAME
.BeginGroup = True
End With
ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = False
Sheets("Sorted").Activate
Exit Sub
ErrorHandler:
MsgBox "Error " & Err.Number & vbCr & Err.Description
Exit Sub
End Sub
'Code to delete the toolbar called by workbook close:
Sub DeleteToolbar()
On Error Resume Next
CommandBars("Network processing").Delete
CommandBars("TradeDoublerTemp").Delete
End Sub
and of course: -
Option Explicit
Private Sub Workbook_Open()
AddNewToolBar
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteToolbar
End Sub
First of all, does anyone have any ideas what might be causing a problem for my colleague?
And what difference does it make having
Temporary:=True or
Temporary:=False when the toolbars are deleted on close? (I currently have one toolbar set to True and one to False and can't see what difference it makes.
Thanks in anticipation.
If anyone needs to see my document please PM me; it's 3 MB so too large to attach here. Please bear in mind also that I am unable to use forum email because I have not made 5 posts yet.
I implemented a variation of lucas' code on my own PC and have been using it very successfully for 3-4 weeks. I just passed it on to a colleague who is distant from me - indeed this is why it was required, easier than trying to talk him through building the toolbars (2 with 26 buttons between them). It worked ok on his laptop but when he put it on his PC he said: -
The downloaded Master seems completely dead. I cannot open anything file, view, format tools, or indeed change pages. ... I left the Master open for half an hour just in case, but still no luck. ... It seemed like the macro was taking a million years to run, yes, and also when I shut it down it seemed to try and load another sheet below for a second.
My 2 new toolbars are positioned at the top so I think '... load another sheet below ...' might be the effect of the sheet moving up screen as they are deleted on close.
Something in the discussion I have puzzled over too. Lucas says it is not necessary to delete the toolbars as they are temporary, and yet there is code in there to delete on close. (I understand why it is necessary to delete at the beginning in case the toolbar already exists.)
My code follows: -
Sub AddNewToolBar()
' This procedure creates a new temporary toolbar.
Dim COMBAR As CommandBar, COMBARCONTRL As CommandBarControl
Dim NETWORK, MACRONAME As String
Application.ScreenUpdating = False
On Error GoTo ErrorHandler
' Create a new toolbar and make it visible.
On Error Resume Next
'Delete the toolbar if it already exists
CommandBars("Network processing").Delete
Set COMBAR = CommandBars.Add(Name:="Network processing", Position:= _
msoBarTop, Temporary:=True)
COMBAR.Visible = True
' Create a button with text on the bar and set some properties.
Sheets("lookup IDs").Select
Range("N1").Activate
Do While ActiveCell.Value <> ""
NETWORK = ActiveCell.Value
MACRONAME = ActiveCell.Offset(0, 1).Value
Set COMBARCONTRL = COMBAR.Controls.Add(Type:=msoControlButton)
With COMBARCONTRL
.Caption = NETWORK
.Style = msoButtonCaption
.TooltipText = "Run " & MACRONAME
'the onaction line tells the button to run a certain macro
.OnAction = "PERSONAL.XLS!" & MACRONAME
.BeginGroup = True
End With
ActiveCell.Offset(1, 0).Select
Loop
'Create another toolbar, as above
CommandBars("TradeDoublerTemp").Delete
Set COMBAR = CommandBars.Add(Name:="TradeDoublerTemp", Position:= _
msoBarTop, Temporary:=False)
COMBAR.Visible = True
Sheets("lookup IDs").Select
Range("Q1").Activate
Do While ActiveCell.Value <> ""
NETWORK = ActiveCell.Value
MACRONAME = ActiveCell.Offset(0, 1).Value
Set COMBARCONTRL = COMBAR.Controls.Add(Type:=msoControlButton)
With COMBARCONTRL
.Caption = NETWORK
.Style = msoButtonCaption
.TooltipText = "Run " & MACRONAME
'the onaction line tells the button to run a certain macro
.OnAction = "PERSONAL.XLS!" & MACRONAME
.BeginGroup = True
End With
ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = False
Sheets("Sorted").Activate
Exit Sub
ErrorHandler:
MsgBox "Error " & Err.Number & vbCr & Err.Description
Exit Sub
End Sub
'Code to delete the toolbar called by workbook close:
Sub DeleteToolbar()
On Error Resume Next
CommandBars("Network processing").Delete
CommandBars("TradeDoublerTemp").Delete
End Sub
and of course: -
Option Explicit
Private Sub Workbook_Open()
AddNewToolBar
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteToolbar
End Sub
First of all, does anyone have any ideas what might be causing a problem for my colleague?
And what difference does it make having
Temporary:=True or
Temporary:=False when the toolbars are deleted on close? (I currently have one toolbar set to True and one to False and can't see what difference it makes.
Thanks in anticipation.
If anyone needs to see my document please PM me; it's 3 MB so too large to attach here. Please bear in mind also that I am unable to use forum email because I have not made 5 posts yet.