Solved

toolbar code can't find my macros

Posted on 2011-03-16
9
252 Views
Last Modified: 2012-05-11
excel vba,

I have used the ciode below in reference to another question:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_23184174.html#a35148450

I keep getting a   <sheetname>!OpenPrebuilts   macro not found

 ??







Sub SetUpCbars()
    Dim ctl As CommandBarControl
    On Error Resume Next
    Set cbr = Application.CommandBars(gcstrAPP_NAME)
    If Not cbr Is Nothing Then Exit Sub
    Set cbr = Application.CommandBars.Add(Name:=gcstrAPP_NAME, Position:=msoBarFloating, MenuBar:=False, temporary:=True)
    Set ctl = cbr.Controls.Add(Type:=msoControlButton)
    With ctl
        .Caption = "Prebuilts"
        .OnAction = "OpenPrebuilts"
        .Style = msoButtonCaption
    End With
    Set ctl = cbr.Controls.Add(Type:=msoControlButton)
    With ctl
        .Caption = "Extended Search"
        .OnAction = "OpenExtSearch"
        .Style = msoButtonCaption
    End With
    Set ctl = cbr.Controls.Add(Type:=msoControlButton)
    With ctl
        .Caption = "Reset Project"
        .OnAction = "ResetProject"
        .Style = msoButtonCaption
    End With
    cbr.Visible = True
End Sub
Sub CleanUpCBars()
    On Error Resume Next
    Application.CommandBars(gcstrAPP_NAME).Delete
End Sub
Sub HideBar()
   On Error Resume Next
   Application.CommandBars(gcstrAPP_NAME).Visible = False
End Sub
Sub ShowBar()
   On Error Resume Next
   Application.CommandBars(gcstrAPP_NAME).Visible = True
End Sub
Sub OpenExtSearch()
frmExpSearch.Show
Sheets("sheet1").Activate
 ' to be considered for error entry
 ActiveCell.Offset(0, 0).Select
'frmiCart.Show
End Sub

Sub OpenPrebuilts()
frmPrebuilts.Show
End Sub

Sub ResetProject()
Dim mysheet As Worksheet

' STILL INVESTIGATING. THIS CAUSES HAVOC FOR SOME REASON
'ThisWorkbook.Names("DeleteData").RefersToRange = ""
   ' SIMPLE BUT WORKS
  ' new code 01-06-2006
' make sure first
    Application.ScreenUpdating = False
    Application.EnableEvents = False
'Range("A2:AP65536").Select
 ' temp unprotect
' get rid of the data
  ActiveSheet.Unprotect
   Rows("2:65000").Select
     Selection.ClearFormats
     Selection.ClearContents
          Selection.NumberFormat = "@"
' wrap text richtext description
FormatCellsTest
' Protect Sheet Again
'  ProtectColumns
    Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Open in new window

0
Comment
Question by:fordraiders
  • 6
  • 3
9 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35148614
Where is that code located?
0
 
LVL 3

Author Comment

by:fordraiders
ID: 35148866
WORKBOOK OPEN EVENT



had This on a module1
Sub OpenExtSearch()
frmExpSearch.Show
Sheets("sheet1").Activate
 ' to be considered for error entry
 ActiveCell.Offset(0, 0).Select
'frmiCart.Show
End Sub

Sub OpenPrebuilts()
frmPrebuilts.Show
End Sub

Sub ResetProject()
Dim mysheet As Worksheet

' STILL INVESTIGATING. THIS CAUSES HAVOC FOR SOME REASON
'ThisWorkbook.Names("DeleteData").RefersToRange = ""
   ' SIMPLE BUT WORKS
  ' new code 01-06-2006
' make sure first
    Application.ScreenUpdating = False
    Application.EnableEvents = False
'Range("A2:AP65536").Select
 ' temp unprotect
' get rid of the data
  ActiveSheet.Unprotect
   Rows("2:65000").Select
     Selection.ClearFormats
     Selection.ClearContents
          Selection.NumberFormat = "@"
' wrap text richtext description
FormatCellsTest
' Protect Sheet Again
'  ProtectColumns
    Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35148907
Try this:
Sub SetUpCbars()  
    Dim ctl As CommandBarControl  
    On Error Resume Next  
    Application.CommandBars(gcstrAPP_NAME).Delete
    On Error Goto 0
    Set cbr = Application.CommandBars.Add(Name:=gcstrAPP_NAME, Position:=msoBarFloating, MenuBar:=False, temporary:=True)  
    Set ctl = cbr.Controls.Add(Type:=msoControlButton)  
    With ctl  
        .Caption = "Prebuilts"  
        .OnAction = "'" & thisworkbook.name & "'!OpenPrebuilts"  
        .Style = msoButtonCaption  
    End With  
    Set ctl = cbr.Controls.Add(Type:=msoControlButton)  
    With ctl  
        .Caption = "Extended Search"  
        .OnAction = "'" & thisworkbook.name & "'!OpenExtSearch"  
        .Style = msoButtonCaption  
    End With  
    Set ctl = cbr.Controls.Add(Type:=msoControlButton)  
    With ctl  
        .Caption = "Reset Project"  
        .OnAction = "'" & thisworkbook.name & "'!ResetProject"  
        .Style = msoButtonCaption  
    End With  
    cbr.Visible = True  
End Sub  

Open in new window


I assume you have set the gcstrAPP_NAME constant somewhere?
0
 
LVL 3

Author Comment

by:fordraiders
ID: 35149372
rorya,
here is what it is saying

ifinal-picerror.png
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 3

Author Comment

by:fordraiders
ID: 35149413
rorrya,

"thisworkbook"

Option Explicit
Public mMfrnumSelect As Boolean
Private cbr As CommandBar
Private Const gcstrAPP_NAME As String = "iFinal_ToolBar_Menu"

Won't take
Public Const gcstrAPP_NAME As String = "iFinal_ToolBar_Menu"


0
 
LVL 3

Author Comment

by:fordraiders
ID: 35149751
ok put the subs in Sheet1...

changed the 3 lines codes:
.OnAction = "'" & ThisWorkbook.Name & "'!Sheet1.OpenPrebuilts"

Added sheet1.<name of macro>


0
 
LVL 3

Author Comment

by:fordraiders
ID: 35149758
post answer back and I'll close it ...
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 35149931
FYI, this line:
Public Const gcstrAPP_NAME As String = "iFinal_ToolBar_Menu"


should be in a normal module, not in the ThisWorkbook module.
0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 35151127
Thanks !
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Manually enter date in datepicker 24 37
VBA to Find/Replace within a specified range in a Workbook 9 14
Excel 17 37
tricky if formula 2 0
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now