How to stop macro in quick access toolbar from opening original workbook

[ThomasOES]

Hello

I've created a read only workbook that gets saved as another file name. I've added a macro

to the quick access toolbar​

that opens a userform. However, when in a workbook that is saved as a different name, the macro on the quick access toolbar always opens the original workbook with the userform in it. If I open the macro menu and select the "open userform macro" the userform appears in the current workbook. I would like to have a button on the quick access toolbar that initiates the macro that opens the userform and doesn't open the original workbook.

Thanks for any help
Tom

 

[My Aswer Is This]

Try this:
1. Create the Userform you want.
2. Select the Userform in the Vba Project window and drag it into your Personal Workbook.
3. Create a Macro that Opens the UserForm
Something like this:

Userform1.Show Modeless

Now in the vba Project window drag this module into your Personal Workbook

Now Add the Macro that opens the Userform to your QAT

You will asked when you quit excel if you want to save changes to Personal Workbook. Choose Yes.

Now you will be able to display that Userform in any workbook.

 

[ThomasOES]

Thanks. The userform now opens in any workbook. However, the user form contains command buttons which initiate macros. When the command buttons are clicked the error message "sub not defined appears". Also, the macro list isn't populated with the many macros the userform is coordinated with. Do I need to import all the associated macros into the personal workbook? Or is there a way to have the macros from the personal workbook coordinate with the macros in the vba project workbook?

 

[My Aswer Is This]

I assume you have Macros in the userform. So if you click on a Command button the macro associated with that button should be right there in that Userform. When you put the userform into the Personal folder the macro would now be in the Personal Workbook also.

Unless for some reason you decide to Just put the Button in the Userform and had the button then call a macro that was not in the userform. Not sure why you would want to do that.

For example if you have a button on the Userform.

You double click on the button and put in something like:

Msgbox "Hello"

Well then any time you click that button in the userform the message Hello should popup.

 

[ThomasOES]

I'll edit a copy of my workbook to follow your advice. I do often call macros with a command button click. I was editing the program so much that it was easier to open the macro rather than open visual basic then view userform code. However, the workbook has very many macros and thousands of lines of code. I think the only way to ensure all work together is to put the entire list of macros and objects into the personal workbook.

 

[My Aswer Is This]

I always try things like this starting small.
If you have hundreds of macro's with thousands of line of code.
I would move one or two macros to the Personal Folder and see how things work.
Before doing that with hundreds of macros and find this does not work for you.

And the only macros I would think you would need in your personal Workbook would be those that you use in more then one workbook.

Give me a example of a Macro you may use in more then one workbook.
And wow. Thousands of line of code.

You must be really good at writing code or very new to writing code.

I have never written any code that is more then 50 lines of code.
If your new to writing code and have thousands of line of code in one Macro
Please show me one those Macros.

Here is something I have done.
I have maybe 50 Macros I use all the time in different Workbooks.

1. I created a Userform
2. I created a Multipage on my Userform.
3. A Multipage is like have a UserForm on a Userform
4. And you can have numerous Multipage pages
5. A Multipage Page is like a sheet on A workbook
6. You have as many pages as you want
7. I Have about 6 Multipage pages each page being a separate category of Macros.
8. On each Multipage Page I have about 20 Option buttons.
9. I give the Option Button a caption telling me what that Option button does.
10. If I click on the Option Button with a caption that says "Hello" Then a script assigned to that option button will run which displays a Message Box that says Hello

This is just a example.

You may want to consider this.
It keeps me from having to remember 40 different shortcut keys to run 40 different Macros.

And you can just popup this Userform any time you want on any Workbook if it's in your Personal Workbook and the scripts in the Userform

 

[ThomasOES]

Hello,

This is a sample of some of the code. I like to format a userform with vba. I usually call a macro with a button click, but sometimes I leave the code in the userform. As you can see, the userform calls several macros. And the macros called by the userform calls even more macros. The entire project just kept growing. The program is to analyze chemical composition of metals. I use the userform as a quasi-toolbar. My original post was the need to show the userform when needed. As you can see, I finally settled on instructing the user to simply press Ctrl m. The better solution would be a floating toolbar if possible.

Sub ShowE826_Ctrl()
'DISPLAY USER FORM TO ASSEMBLE MATERIAL STUDY
E826_Ctrl.Show Modal
With E826_Ctrl
    .Height = 440
    .Width = 360
End With
With E826_Ctrl.Label1
    .TextAlign = fmTextAlignCenter
    .Top = 4
    .Left = 0
    .Width = 360
    .FontSize = 24
    .Height = 26
    .Caption = "Brammer Standard"
    .Font.Bold = True
    .ForeColor = vbBlue
End With
With E826_Ctrl.Label2
    .TextAlign = fmTextAlignCenter
    .Top = 38
    .Left = 0
    .Width = 360
    .Height = 24
    .FontSize = 22
    .Caption = "E826 Lot Qualification"
    .Font.Bold = True
    .ForeColor = vbBlue
End With
With E826_Ctrl.CommandButton1
    .Caption = "Initiate New Spreadsheet"
    .Font.Bold = True
    .FontSize = 12
    .AutoSize = True
    .Left = (E826_Ctrl.InsideWidth - .Width) / 2
    .Top = 80
End With
With E826_Ctrl.CommandButton6
    .Caption = "Print " & _
        Range("ReportName").Value & " Sequence"
    .Font.Bold = True
    .FontSize = 12
    .AutoSize = True
    .Left = (E826_Ctrl.InsideWidth - .Width) / 2
    .Top = 120
End With
With E826_Ctrl.CommandButton3
    .Caption = "Import " & _
        Range("ReportName").Value & " Data"
    .Font.Bold = True
    .FontSize = 12
    .AutoSize = True
    .Left = (E826_Ctrl.InsideWidth - .Width) / 2
    .Top = 160
End With
With E826_Ctrl.CommandButton5
    .Caption = "Print " & _
        Range("ReportName").Value & " Data"
    .Font.Bold = True
    .FontSize = 12
    .AutoSize = True
    .Left = (E826_Ctrl.InsideWidth - .Width) / 2
    .Top = 200
End With
With E826_Ctrl.CommandButton2
    .Caption = "Initiate " & _
        Range("ReportName").Value & " Analysis"
    .Font.Bold = True
    .FontSize = 12
    .AutoSize = True
    .Left = (E826_Ctrl.InsideWidth - .Width) / 2
    .Top = 240
End With
With E826_Ctrl.CommandButton4
    .Caption = "Resume " & _
        Range("ReportName").Value & " Analysis"
    .Font.Bold = True
    .FontSize = 12
    .AutoSize = True
    .Left = (E826_Ctrl.InsideWidth - .Width) / 2
    .Top = 280
End With
With E826_Ctrl.CommandButton7
    .Caption = "Print " & _
        Range("ReportName").Value & " Report"
    .Font.Bold = True
    .FontSize = 12
    .AutoSize = True
    .Left = (E826_Ctrl.InsideWidth - .Width) / 2
    .Top = 320
End With
With E826_Ctrl.Label3
    .TextAlign = fmTextAlignCenter
    .Top = 380
    .Left = 0
    .Width = 360
    .Height = 24
    .FontSize = 10
    .Caption = "Press Ctrl m to show this form"
    .Font.Bold = True
    .ForeColor = vbRed
End With
End Sub

[CODE]Private Sub CommandButton1_Click()
'DISPLAY USER FORM TO ASSEMBLE MATERIAL STUDY
Me.Hide
Call Show_ProjInfo
End Sub
Private Sub CommandButton2_Click()
Me.Hide
Call DataCalc
End Sub
Private Sub CommandButton3_Click()
Me.Hide
Sheets("Data").Select
Application.CutCopyMode = False
With ActiveSheet.QueryTables. _
Add(Connection:="TEXT;\\Nas-bb\nas-2a\GDS500A\E826 Data\" _
& Range("ReportName").Value & ".csv", _
Destination:=Range("$A$1"))
    '.Name = "BS Test"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = _
     Array(1, 1, 1, 1, 1, 1, 1, 1, 1, _
           1, 1, 1, 1, 1, 1, 1, 1, 1, _
           1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With
Call CreateButton2
End Sub
Private Sub CommandButton4_Click()
Me.Hide
Call DataCalcResume
End Sub
Private Sub Label1_Click()
End Sub
Private Sub CommandButton5_Click()
Me.Hide
Call PrintDat
End Sub
Private Sub CommandButton6_Click()
'PRINT PREVIEW FOR SEQUENCE
Me.Hide
Call PrintArea
End Sub
Private Sub CommandButton7_Click()
'PRINT PREVIEW FOR REPORT
Me.Hide
Call RePrint2
End Sub

[/CODE]

Here is some code that determines if several samples have like chemistry. I included it to give an idea of the scope. There are several more macros of similar size.

Sub ElHom1()
'SET BANNERS FOR MONITOR ANOVA
Range("AI1", "BA100").Clear
Range("AN1").Select
With Selection
        .Value = "Monitor ANOVA"
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Font.Bold = True
        .Font.Color = RGB(240, 0, 0)
End With
Range("HomHead").Copy
Range("AK3").PasteSpecial
Range("HomVal").Copy
Range("AI13").PasteSpecial
Range("AJ4").Select
With Selection
        .Value = "Mon"
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Font.Bold = True
End With
ActiveCell.Copy
ActiveCell.Resize(Range("MonitorTotal").Value).PasteSpecial
Range("AT4").Name = "MonGrandAv"
'APPLY REGRESSION TERMS TO EACH MONITOR BURN
Range("AK4").Select
ActiveCell.FormulaR1C1 = "=Constant1+Factor1*R[-2]C[-18]"
ActiveCell.Copy
Range(ActiveCell, ActiveCell.Offset _
(((Range("MonitorTotal").Value) - 1), 3)).PasteSpecial
'SET BANNERS FOR LOT ANOVA
Range("AN18").Select
With Selection
        .Value = "Lot ANOVA"
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Font.Bold = True
        .Font.Color = RGB(240, 0, 0)
End With
Range("HomHead").Copy
ActiveCell.Offset(2, -3).Select
ActiveCell.PasteSpecial
Range("HomVal").Copy
ActiveCell.Offset _
(((Range("SamplesPieces").Value) + 2), -2).PasteSpecial
'LOCATE START OF LOT SAMPLE DATA
Columns("D:D").Select
Selection.Find(What:="-2", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
'NAME CELL AT BEGINNING OF SAMPLE DATA "PrevCell"
Set PrevCell = ActiveCell
'COPY SAMPLE NAMES INTO LOT ANOVA TABLE
Range(ActiveCell.Offset(0, -3), _
ActiveCell.Offset(((Range("SamplesPieces").Value) - 1), -3)).Copy
Range("AJ21").PasteSpecial
Range("AV4").PasteSpecial
'SET BANNER FOR LOT SAMPLE DATA BLOCK
Range("AX1").Select
With Selection
        .Value = "Lot Sample Drift Data"
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        .Font.Bold = True
        .Font.Color = RGB(240, 0, 0)
End With
Range("AT21").Name = "LotGrandAv"
Range("S1", "V1").Copy
Range("AW3").PasteSpecial
'COPY SAMPLE DATA INTO LOT SAMPLE DRIFT DATA BLOCK
PrevCell.Select
Range(ActiveCell.Offset(0, 1), _
ActiveCell.Offset(((Range("SamplesPieces").Value) - 1), 4)).Copy
Range("AW4").PasteSpecial
'APPLY REGRESSION TERMS TO EACH LOT SAMPLE BURN
'AND PLACE INTO ANOVA TABLE
Range("AK21").Select
ActiveCell.FormulaR1C1 = "=Constant1+Factor1*R[-17]C[12]"
ActiveCell.Copy
Range(ActiveCell, ActiveCell.Offset _
(((Range("SamplesPieces").Value) - 1), 3)).PasteSpecial
'CALCULATE MONITOR HOMONGENEITY PARAMETERS
Range("AO4").Formula = "=Sum(AK4:AN4)"
Range("AP4").Formula = "=(AO4^2)"
Range("AQ4").Formula = "=Sum(AK4^2,AL4^2,AM4^2,AN4^2)"
Range("AR4").Formula = "=Average(AK4:AN4)"
Range("AT4") = Application.Average(Range("AK4", Range("AK4").Offset _
(((Range("MonitorTotal").Value) - 1), 3)))
Range("AS4").Formula = "=(AR4-MonGrandAv)"
Range("AO4", "AS4").Copy
Range("AO4").Resize(Range("MonitorTotal").Value).PasteSpecial
Range("AK4", Range("AK4"). _
Offset(((Range("MonitorTotal").Value) - 1), 8)).Select
'Call RounData
Range("AK14").Formula = "=Sum(AK4:AK12)"
Range("AK14").Copy
Range("AL14", "AN14").PasteSpecial
Range("AO14").Formula = "=Sum(AK14:AN14)"
Range("AK16").Formula = "=(AK14^2)"
Range("AK16").Copy
Range("AL16", "AN16").PasteSpecial
Range("AO16").Formula = "=Sum(AK16:AN16)"
Range("AI14").Name = "MonFree"
Range("MonFree").Formula = "=3*(MonitorTotal-1)"
Range("AJ14") = Range("MonitorTotal")
'MONITOR CRITICAL VALUE FROM TABLE
Sheets("Banners").Select
Range("degfree5").Select
Selection.Find(What:=Range("MonitorTotal").Value, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Offset(0, 1).Copy
Sheets(Range("elcell").Value).Select
Range("AJ16").PasteSpecial
Sheets("Banners").Select
Range("degfree1").Select
Selection.Find(What:=Range("MonitorTotal").Value, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Offset(0, 1).Copy
Sheets(Range("elcell").Value).Select
Range("AJ18").PasteSpecial
'CALCULATE MONITOR SSt
Range("AP14") = (Application.Sum(Range("AP4"). _
Resize(Range("MonitorTotal").Value)) / 4) - ((Range("AO14") ^ 2) / (4 * Range("AJ14")))
'CALCULATE MONITOR SSb
Range("AQ14") = ((Application.Sum(Range("AK16", "AN16"))) _
/ (Range("AJ14"))) - ((((Range("AO14")) ^ 2) / (4 * Range("AJ14"))))
'CALCULATE MONITOR SST
Range("AP16") = (Application.Sum(Range("AQ4").Resize(Range("MonitorTotal").Value)) _
- (((Range("AO14") ^ 2)) / (4 * Range("AJ14"))))
'CALCULATE MONITOR S
Range("AQ16").Value = Sqr(((Range("AP16")) - (Range("AQ14")) - (Range("AP14"))) _
/ (3 * (Range("MonitorTotal") - 1)))
'CALCULATE MONITOR W95
Range("AR14").Value = Range("AJ16") * Range("AQ16") / 2
Range("AR14").Name = "Mon95WVal"
'CALCULATE MONITOR W99
Range("AT16").Value = Range("AJ18") * Range("AQ16") / 2
Range("AT16").Name = "Mon99WVal"
'CALCULATE MONITOR T
Range("AR16").Value = WorksheetFunction.Max(Range("AR4").Resize(Range("MonitorTotal").Value)) _
- WorksheetFunction.Min(Range("AR4").Resize(Range("MonitorTotal").Value))
Range("AR16").Name = "MonTVal"
'MONITOR MAXIMUM RUN AVERAGE
Range("AR18").Value = WorksheetFunction.Max(Range("AR4").Resize(Range("MonitorTotal").Value))
Range("AR18").Name = "MonMaxW"
'MONITOR MINIMUM RUN AVERAGE
Range("AS18").Value = WorksheetFunction.Min(Range("AR4").Resize(Range("MonitorTotal").Value))
Range("AS18").Name = "MonMinW"
'MONITOR MAXIMUM (RUN AVERAGE - MONITOR GRAND AVERAGE)
Range("AS14").Value = WorksheetFunction.Max(Range("AS4").Resize(Range("MonitorTotal").Value))
'MONITOR MINIMUM (RUN AVERAGE - MONITOR GRAND AVERAGE)
Range("AS16").Value = WorksheetFunction.Min(Range("AS4").Resize(Range("MonitorTotal").Value))
'MONITOR STANDARD DEVIATION
Range("AT14").Value = WorksheetFunction. _
StDev(Range("AR4").Resize(Range("MonitorTotal").Value))
Range("AT14").Name = "MonStDev"
'CALCULATE LOT HOMONGENEITY PARAMETERS
Range("AO21").Formula = "=Sum(AK21:AN21)"
Range("AP21").Formula = "=(AO21^2)"
Range("AQ21").Formula = "=Sum(AK21^2,AL21^2,AM21^2,AN21^2)"
Range("AR21").Formula = "=Average(AK21:AN21)"
Range("AT21") = Application.Average(Range("AK21", Range("AK21").Offset _
(((Range("SamplesPieces").Value) - 1), 3)))
Range("AS21").Formula = "=(AR21-LotGrandAv)"
Range("AO21", "AS21").Copy
Range("AO21").Resize(Range("SamplesPieces").Value).PasteSpecial
Range("AK21", Range("AK21"). _
Offset(((Range("SamplesPieces").Value) - 1), 8)).Select
'Call RounData
'SUM THEN SQUARE EACH LOT RUN
Range("AK" & Range("SamplesPieces").Value + 23).Select
ActiveCell = WorksheetFunction. _
Sum(Range("AK21").Resize(Range("SamplesPieces").Value))
ActiveCell.Offset(2, 0).FormulaR1C1 = "=R[-2]C[0]^2"
Range("AL" & Range("SamplesPieces").Value + 23).Select
ActiveCell = WorksheetFunction. _
Sum(Range("AL21").Resize(Range("SamplesPieces").Value))
ActiveCell.Offset(2, 0).FormulaR1C1 = "=R[-2]C[0]^2"
Range("AM" & Range("SamplesPieces").Value + 23).Select
ActiveCell = WorksheetFunction. _
Sum(Range("AM21").Resize(Range("SamplesPieces").Value))
ActiveCell.Offset(2, 0).FormulaR1C1 = "=R[-2]C[0]^2"
Range("AN" & Range("SamplesPieces").Value + 23).Select
ActiveCell = WorksheetFunction. _
Sum(Range("AN21").Resize(Range("SamplesPieces").Value))
ActiveCell.Offset(2, 0).FormulaR1C1 = "=R[-2]C[0]^2"
'SUM THE SUMS OF EACH LOT RUN THEN SQUARE
Range("AO" & Range("SamplesPieces").Value + 23).Select
ActiveCell = Application.Sum(Range("AK" & Range("SamplesPieces").Value + 23, _
"AN" & Range("SamplesPieces").Value + 23))
ActiveCell.Offset(2, 0).FormulaR1C1 = "=R[-2]C[0]^2"
'LOT DEGREE OF FREEDOM AND SAMPLE TOTAL
Range("AI" & Range("SamplesPieces").Value + 23).Select
ActiveCell.Name = "LotFree"
Range("LotFree").Formula = "=3*(SamplesPieces-1)"
Range("AJ" & Range("SamplesPieces").Value + 23) = Range("SamplesPieces")
'LOT CRITICAL VALUE FROM TABLE
Sheets("Banners").Select
Range("degfree5").Select
Selection.Find(What:=Range("SamplesPieces").Value, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Offset(0, 1).Copy
Sheets(Range("elcell").Value).Select
Range("AJ" & Range("SamplesPieces").Value + 25).PasteSpecial
Sheets("Banners").Select
Range("degfree1").Select
Selection.Find(What:=Range("SamplesPieces").Value, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Offset(0, 1).Copy
Sheets(Range("elcell").Value).Select
Range("AJ" & Range("SamplesPieces").Value + 27).PasteSpecial
'CALCULATE LOT SSt
Range("AP" & Range("SamplesPieces").Value + 23).Select
ActiveCell = (Application.Sum(Range("AP21").Resize(Range("SamplesPieces").Value))) / 4 - _
((ActiveCell.Offset(0, -1) ^ 2) / (4 * Range("SamplesPieces")))
'CALCULATE LOT SSb
Range("AQ" & Range("SamplesPieces").Value + 23).Select
ActiveCell = Application.Sum(Range("AK" & Range("SamplesPieces").Value + 25, _
"AN" & Range("SamplesPieces").Value + 25)) / _
(Range("SamplesPieces")) - ((Range("AO" & Range("SamplesPieces").Value + 23)) ^ 2) / _
(4 * Range("SamplesPieces"))
'CALCULATE LOT SST
Range("AP" & Range("SamplesPieces").Value + 25).Select
ActiveCell = (Application.Sum(Range("AQ21").Resize(Range("SamplesPieces").Value)) _
- ((Range("AO" & Range("SamplesPieces").Value + 23)) ^ 2) / _
(4 * Range("SamplesPieces")))
'CALCULATE LOT S
Range("AQ" & Range("SamplesPieces").Value + 25).Select
ActiveCell.Value = Sqr _
((Range("AP" & Range("SamplesPieces").Value + 25) - _
Range("AQ" & Range("SamplesPieces").Value + 23) - _
Range("AP" & Range("SamplesPieces").Value + 23)) / _
(3 * (Range("SamplesPieces") - 1)))
'CALCULATE LOT W95
Range("AR" & Range("SamplesPieces").Value + 23).Select
ActiveCell.Value = Range("AJ" & Range("SamplesPieces").Value + 25) * _
Range("AQ" & Range("SamplesPieces").Value + 25) / 2
ActiveCell.Name = "Lot95WVal"
'CALCULATE LOT W99
Range("AT" & Range("SamplesPieces").Value + 25).Select
ActiveCell.Value = Range("AJ" & Range("SamplesPieces").Value + 27) * _
Range("AQ" & Range("SamplesPieces").Value + 25) / 2
ActiveCell.Name = "Lot99WVal"
'CALCULATE LOT T
Range("AR" & Range("SamplesPieces").Value + 25).Select
ActiveCell.Value = WorksheetFunction.Max(Range("AR21").Resize(Range("SamplesPieces").Value)) _
- WorksheetFunction.Min(Range("AR21").Resize(Range("SamplesPieces").Value))
ActiveCell.Name = "LotTVal"
'LOT MAXIMUM RUN AVERAGE
Range("AR" & Range("SamplesPieces").Value + 27).Select
ActiveCell.Value = WorksheetFunction.Max(Range("AR21").Resize(Range("SamplesPieces").Value))
ActiveCell.Name = "LotMaxW"
'LOT MINIMUM RUN AVERAGE
Range("AS" & Range("SamplesPieces").Value + 27).Select
ActiveCell.Value = WorksheetFunction.Min(Range("AR21").Resize(Range("SamplesPieces").Value))
ActiveCell.Name = "LotMinW"
'LOT MAXIMUM (RUN AVERAGE - MONITOR GRAND AVERAGE)
Range("AS" & Range("SamplesPieces").Value + 23).Select
ActiveCell.Value = WorksheetFunction.Max(Range("AS21").Resize(Range("SamplesPieces").Value))
'LOT MINIMUM (RUN AVERAGE - MONITOR GRAND AVERAGE)
Range("AS" & Range("SamplesPieces").Value + 25).Select
ActiveCell.Value = WorksheetFunction.Min(Range("AS21").Resize(Range("SamplesPieces").Value))
'LOT STANDARD DEVIATION
Range("AT" & Range("SamplesPieces").Value + 23).Select
ActiveCell.Value = WorksheetFunction. _
StDev(Range("AR21").Resize(Range("SamplesPieces").Value))
ActiveCell.Name = "LotStDev"
'INITIATE REPORT COLUMN
Call ElRep
'FORMAT SAMPLE DATA INTO REPORT COLUMN
Call SampForm
'FORMAT ELEMENT REPORT SYMBOL
Call ElRepForm
'SAVE SHEET AS VALUES ONLY
ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
'SEND REPORT COLUMN TO REPORT SHEET
Call RepCols
'SAVE REPORT SHEET AS VALUES ONLY
ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
Sheets("Report").Select
End Sub

I have about one year experience with vba. This site has been crucial to having a working program. I'm now trying to make the program as user friendly as possible. Thus the need for a custom floating toolbar.

Thanks

Tom

 

[My Aswer Is This]

Wow.
You have a lot going on here.
In your first script it looks like your building your userform using Vba.
I have never tried to build a Userform using Vba.
I always build mine manually.
But I guess you have your reasons.

Not sure why you would think a floating Tool Bar would be easier then using a Userform to launch your Macros.

You mentioned wanting to use Userform in your original post.

I have never built a floating Tool Bar

I would think you would have to have some type way of knowing what each icon on this toll bar would do and then assign a macro to each icon.

I explained how I do things with my Userform but I assume you did not like that suggestion.

A userform looks like to me is the same as a floating ToolBar but then I'm not exactly sure how a floating tool bar would look.

But in either case if you want to run hundreds of different macros in which ever is the active workbook at any given time I believe the macros would have to be in your Personal workbook

I would also think creating a Userform combobox and load all the macro names into the combobox.

And then if from the combobox you choose Mom the script named Mom would run would be a ideal of mine.

So if your now thinking you want what is know as a Floating Tool Bar I'm not able to help you with that.
That is something I have no knowledge about

 

[ThomasOES]

Thanks

I'll use a copy of the program and put everything into the personal workbook. I like the idea about the combobox. I appreciate the feedback. Thanks for observing my code.

Tom

 

[My Aswer Is This]

If you use a Combobox to run scripts.
Your code would look something like this:

Private Sub ComboBox1_Change()
'Modified  10/30/2018  1:26:11 PM  EDT
On Error GoTo M
Application.Run ComboBox1.Value
Exit Sub
M:
MsgBox "We have no macro named  " & ComboBox1.Value
End Sub
Private Sub UserForm_Initialize()
'Modified  10/30/2018  1:26:11 PM  EDT
With ComboBox1
.AddItem "Us"
.AddItem "You"
End With
End Sub

Us and You would be the names of your scripts for example.