Run a macro with a command button in a new workbook that is created from another workbook.

[seattletimebandit]

I don't think my Descriptive Title is as clear as I need it to be, but...

If you've seen this post on another forum, yup, I did. Just covering my bases...

I have a workbook that has a worksheet that runs a macro to transpose a range of data (Sub TransposeLabData). At the end of the vb code, I have the transposed data moved to a new workbook ("New Table") with a name for the worksheet (named from user input). Works great, saves it to my Documents folder, no issues.

I have another bit of code (Sub GreaterThanCULs) that scans the transposed range of data and bolds and/or highlights cells based on criteria. That works well if I run the macro by selecting "Macros" in the "Developer" tab of the Excel ribbon.

BUT, I want to add a Command Button to run "Sub GreaterThanCULs" in the newly created worksheet. I successfully was able to have the new sheet open with the transposed data and the Command Button named "Bold Hits & Highlight CULs". However, when clicking the button, nothing happens. It doesn't call the "Sub GreaterThanCULs" code.

Is it because the code is in the module "Sub TransposeLabData" in the first worksheet? If that's the case, how do I call the "Sub GreaterThanCULs" code in the original workbook from the newly created worksheet?

Am I making any sense?

When trying to figure out how to do this, I searched lots of forums and found the following "Set Obj" snippet used to add the command button and call the code.

Thanks in advance! All of you that help us rookie coders really appreciate you...

And if you need me to upload a copy of the TransposeData.xlsm workbook, I will do so.

The bit that creates the button and new workbook:

With Activesheet
            Set Obj = .OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
                          Link:=False, DisplayAsIcon:=False, Left:=400, _
                         Top:=400, Width:=200, Height:=35)

            Obj.Name = "GreaterThanCULs" '<< name must match code below...
            Obj.Object.Caption = "Bold Hits & Highlight CULs"
         
            Code = "Sub GreaterThanCULs()" & vbCrLf & _
            "   Call GreaterThanCULs" & vbCrLf & _
            "End Sub"
        
            'With .Parent.VBProject.VBComponents(.CodeName).CodeModule
             '  .insertlines .CountOfLines + 1, Code
        'End With
     End With
     
                   
        
    Activesheet.Move                    'Move newly created "Transposed Data" worksheet to a new workbook
    ActiveWorkbook.SaveAs "New Table" 'Save new workbook (should save to default "Documents" folder on User's computer
    With activeworkboook.NewTable
    
    End With

The code to call:

Sub GreaterThanCULs()
    Application.ScreenUpdating = False
    Dim lastColumn As Integer
    Dim c1 As Range
    Dim c2 As Range
    For Each c1 In Range("b4:b100")
        If c1 <> "" And IsNumeric(c1) Then
        lastColumn = Activesheet.Cells(c1.Row, Columns.Count).End(xlToLeft).Column
            For Each c2 In Range(Cells(c1.Row, 3), Cells(c1.Row, lastColumn))
                If c2 <> "" And IsNumeric(c2) And c2 >= c1 Then
                    c2.Interior.ColorIndex = 28
                End If
            Next c2
        End If
    Next c1
    
    For Each c1 In Range("b4:b100")
        If c1 = "" Or IsNumeric(c1) Then
        lastColumn = Activesheet.Cells(c1.Row, Columns.Count).End(xlToLeft).Column
            For Each c2 In Range(Cells(c1.Row, 3), Cells(c1.Row, lastColumn))
                If Right(c2.Value, 1) = "U" Then
              c2.Font.Bold = False
        Else: c2.Font.Bold = True
                End If
            Next c2
        End If
    Next c1
    Application.ScreenUpdating = True
End Sub

NOTE: this bit below when allowed to run, has Excel tell me that the "VB Project cannot be saved in macro-free workbooks". If it's hidden, like in the above code, I don't get the "macro-free" warning, but the code, of course, still doesn't run.

So I'm guessing that I need to have my new worksheet be formatted as "New Table.xlsm"? Then will the code work? How do I save the new worksheet with the macro friendly .xlsm extension? Haven't been able to figure that out or find it on the web.

With .Parent.VBProject.VBComponents(.CodeName).CodeModule
               .insertlines .CountOfLines + 1, Code
        End With

 

[seattletimebandit]

To All,

I had cross-posted this request on https://www.excelforum.com/excel-pr...ok-that-is-created-from-another-workbook.html.

I wasn't aware of this infraction and let the moderator at Excel Forum know where I had posted (here). My apologies to the protocols at Excel Forum and Mr. Excel.com.

I'm not able to find where I could delete a post that I made, so in lieu of deleting, please accept my providing the cross-post link as my way of informing the community.

stb

 

[Peter_SSs]

Whilst we prefer not to deal with cross-posting it is allowed here but we do like you to provide links to your cross-posts & you have done that so all is good.
Details of that rule (13) as well as all our other rules can be found via the 'Board FAQ' drop-down near the top left of the forum pages.

 

[seattletimebandit]

Additional info:

I did find that by selecting "Design Mode" in the Developer Ribbon, I can see that the macro assigned to my new button has this:

Private Sub GreaterThanCULs_Click()
   Call GreaterThanCULs_Click
End Sub

Not clear if it doesn't have all the code because it's not a macro-enabled worksheet, or my call is not correct.