PDA

View Full Version : Problems with macro based on "Simple Floating Toolbar" (lucas)



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.

Bob Phillips
01-01-2008, 09:22 AM
You shouldn't need to delete it if you set Temporary = True, but Lucas is just being (sensibly IMO) cautious.

Try this version of your code and see if you get an error




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 Resume Next
'Delete the toolbar if it already exists
CommandBars("Network processing").Delete

On Error GoTo ErrorHandler
' Create a new toolbar and make it visible.
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

On Error Resume Next
'Create another toolbar, as above
CommandBars("TradeDoublerTemp").Delete

On Error GoTo ErrorHandler
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 = True
Sheets("Sorted").Activate
Exit Sub

ErrorHandler:
Application.ScreenUpdating = True
MsgBox "Error " & Err.Number & vbCr & Err.Description
Exit Sub
End Sub

shirley999
01-01-2008, 09:44 AM
Ah, I see. :)

I will have to wait a couple of days before my colleague can test it but I can see the sense in the changes you've made. I'll report back.

Thanks very much for your help.