Custom Toolbar button not running full sub

[ollienashchapman]

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

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

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

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

 

[ollienashchapman]

IGNORE.... for some reason after closing and opening this is now working.... apologies