Integrating two macros into one

I have a great little form/macro that byandt wrote for me that keeps an option to edit and return visible in a spreadsheet.  The challenge I have is I have two macros that need to be placed into the form/macro and I don't know how to incorporate them into what was sent.

Thanks in advance,

B.
Moving-Buttons-with-the-cellsQ28721.xlsm
Bright01Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
Where do you want the two macros to run?  There's no code in the UserForm except two test messages.
Martin LissOlder than dirtCommented:
As Roy implied it's hard to tell what macro's you want to run. One thing I can say is that if one is called Macro1 and the other is called Macro2 then a 3rd macro can be written like this that will run them both.

Sub Macro3()

' Run Macro1
Macro1

'Run Macro2
Macro2
End Sub

Open in new window

Rgonzo1971Commented:
Hi,

pls try ( inthe form module)

Option Explicit

Private Sub CommandButton1_Click()
'Private Sub CommandButton2_Click()

'If CommandButton1.Caption = "Display More" Then
If CommandButton1.Caption = "Display Questions" Then
    'Your two macros look the same to me except for the name, but
    'reguardless you would put the name of the one that you want to
    'run when the caption says "Expand" here
    Contract_All '
'    Else
    'Expand_All
Else
If CommandButton1.Caption = "Display Details" Then
    'Your two macros look the same to me except for the name, but
    'reguardless you would put the name of the one that you want to
    'run when the caption says "Expand" here
    Expand_All
Else
If CommandButton1.Caption = "Display More Detail" Then
    'Your two macros look the same to me except for the name, but
    'reguardless you would put the name of the one that you want to
    'run when the caption says "Expand" here
    Details_All
End If
End If
End If

End Sub


Private Sub CommandButton2_Click()
Run 'G:\PC Dev Data\PCI Workbench\PCI Workbench v113.xlsm'!Sheet34.ReturntoDirections_Click
End Sub
Sub Contract_All()
'ActiveSheet.Unprotect Password:="jam"
ActiveSheet.Outline.ShowLevels Rowlevels:=1
'ActiveSheet.Protect Password:="jam"
ActiveSheet.EnableSelection = xlUnlockedCells
CommandButton1.Caption = "Display Details"
Range("A3").Activate
End Sub

Sub Expand_All()
'ActiveSheet.Unprotect Password:="jam"
ActiveSheet.Outline.ShowLevels Rowlevels:=2
'ActiveSheet.Protect Password:="jam"
ActiveSheet.EnableSelection = xlUnlockedCells
CommandButton1.Caption = "Display More Detail"
Range("A3").Activate
End Sub
Sub Details_All()
'ActiveSheet.Unprotect Password:="jam"
ActiveSheet.Outline.ShowLevels Rowlevels:=3
'ActiveSheet.Protect Password:="jam"
ActiveSheet.EnableSelection = xlUnlockedCells
CommandButton1.Caption = "Display Questions"
Range("A3").Activate
End Sub

Sub ReturntoDirections_Click()
'This brings up the Directions for review
Sheet34.Visible = xlSheetHidden
Sheet25.Visible = xlSheetVisible
Sheet25.Activate
End Sub

Open in new window

Regards
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Maybe something like this....

Please refer to the attached workbook.
Moving-Buttons-with-the-cellsQ28721.xlsm
byundtMechanical EngineerCommented:
On the Sheet1 code pane, there are two Option Explicit statements. There may only be one of those, and it must be located before any subs or functions. I deleted the second one.

I deleted the HelloWorld subs that had been used to demo the userform. The buttons on the userform now point to your code. I also deleted the ActiveX button and shape on Sheet1 that you had been using to call the macros. I assume that you will want to use the userform buttons for that purpose instead.

If you rightclick the userform in the Project Explorer pane on left side of the VBA Editor, you can choose View Code to see the userform's code pane. I moved most of the code from Sheet1 code pane to the userform code pane.

I pointed the top button to your old CommandButton2_Click sub (which I renamed CommandButton1_Click). I rewrote its code using a Select Case instead of the nested If blocks for ease of understanding.

I made the bottom button on the userform smaller and colored it light blue to match the old Return button on Sheet1. It is worth noting that the ReturnToDirections sub references worksheets that don't exist in the sample workbook. You will need to either add them or revise the sub accordingly.
'This code is on ThisWorkbook code pane
Private Sub Workbook_Open()
If ActiveSheet.CodeName = "Sheet1" Then Modeless
End Sub

Open in new window

'This code is on Sheet1 code pane
Private Sub Worksheet_Activate()
Modeless
End Sub

Private Sub Worksheet_Deactivate()
CancelModeless
End Sub

Open in new window

'All of this code is on UserForm1 code pane
Private Sub CommandButton2_Click()
ReturnToDirections
End Sub

Private Sub CommandButton1_Click()
With CommandButton1
    Select Case .Caption
    Case "Display Questions"
        'Your two macros look the same to me except for the name, but
        'reguardless you would put the name of the one that you want to
        'run when the caption says "Expand" here
        Contract_All '
        .Caption = "Display Details"
        
    Case "Display Details"
        'Your two macros look the same to me except for the name, but
        'reguardless you would put the name of the one that you want to
        'run when the caption says "Expand" here
        Expand_All
        .Caption = "Display More Detail"
     
    Case "Display More Detail"
        'Your two macros look the same to me except for the name, but
        'reguardless you would put the name of the one that you want to
        'run when the caption says "Expand" here
        Details_All
        .Caption = "Display Questions"
    
    End Select
End With

End Sub

Sub Contract_All()
'ActiveSheet.Unprotect Password:="jam"
ActiveSheet.Outline.ShowLevels Rowlevels:=1
'ActiveSheet.Protect Password:="jam"
ActiveSheet.EnableSelection = xlUnlockedCells
Range("A3").Activate
End Sub

Sub Expand_All()
'ActiveSheet.Unprotect Password:="jam"
ActiveSheet.Outline.ShowLevels Rowlevels:=2
'ActiveSheet.Protect Password:="jam"
ActiveSheet.EnableSelection = xlUnlockedCells
Range("A3").Activate
End Sub

Sub Details_All()
'ActiveSheet.Unprotect Password:="jam"
ActiveSheet.Outline.ShowLevels Rowlevels:=3
'ActiveSheet.Protect Password:="jam"
ActiveSheet.EnableSelection = xlUnlockedCells
Range("A3").Activate
End Sub

Sub ReturnToDirections()
'This brings up the Directions for review
Sheet34.Visible = xlSheetHidden
Sheet25.Visible = xlSheetVisible
Sheet25.Activate
End Sub

Open in new window

Moving-Buttons-with-the-cellsQ28721.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
Great Teamwork to all!  This was a horrible "ask" with little detail.  And sorry for being "off the grid" for several days.  In the end, it worked....... required a little trouble shooting to incorporate it but you guys did great and helped me learn more about how to use Forms!

Much thanks,

B.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.