Custom Toolbar button not running full sub

ollienashchapman

New Member
Joined
May 15, 2014
Messages
4
Hi All,

I have created a Sub which does a few things:

1) Ask for a string
2) Queries a db with said string
3) Creates a new sheet on active workbook with the name of said string
4) Past the recordset in the new sheet

If I run the Macro on its own it does everything fine. However i have now created a Custom Toolbar button and assigned the sub to this button. When I run the macro from the button it gets the data from the database but does not create the new sheet. I think it has something to do with my VBA for the command bar?

Any help would be appreciated

Thanks for your time!

Code:

MAIN SUB
Code:
Public Sub GetSolaComp()

'Get product RIC code


myRIC = InputBox("Please provide Index/ETF")


'Get results from the database


GetAllRecordsFromDB (myRIC)


'Creating new sheet named by product


CreateSheet (myRIC)


'Copy recordset to excel sheet


Range("A2").CopyFromRecordset objMyRecordset
 
'Get table headings
 
For intColIndex = 0 To objMyRecordset.Fields.Count - 1
    Range("A1").Offset(0, intColIndex).Value = objMyRecordset.Fields(intColIndex).Name
Next


End Sub

CREATE SHEET FUNCTION

Code:
Public Function CreateSheet(tempRic As String)

'On Error GoTo endThis


Dim ws As Worksheet
sNane = Application.ActiveWorkbook.Name
Application.Workbooks(sNane).Activate
Set ws = Application.ActiveWorkbook.Sheets.Add
    ws.Name = tempRic
    
'endThis:
'   MsgBox "Problems with creating new sheet called " & tempRic & " - " & Err.Description
'End


End Function

CREATING EXCEL COMMAND TOOLBAR BUTTON

Code:
Private Sub Workbook_Open()Start
End Sub


Public Sub Start()


ActiveWorkbookName = Application.ActiveWorkbook.Name
ThisWorkbook.Activate
For Each Bar In Application.CommandBars
    If Bar.Name = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) Then Bar.Delete
Next


Set mybar = CommandBars _
    .Add(Name:=Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4), _
             Position:=msoBarTop, Temporary:=False)
With mybar
   Set newItem = .Controls.Add(Type:=msoControlButton, ID:=1742, Before:=1)
   With newItem
       .Caption = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)
       .OnAction = ThisWorkbook.Path & "\" & ThisWorkbook.Name & "!GetSolaComp"
       .Style = msoButtonIconAndCaption
   End With
   .Visible = True
End With


Application.Workbooks(ActiveWorkbookName).Activate


End Sub
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top