command buttons

pwill

Active Member
Joined
Nov 22, 2015
Messages
406
Hi can anyone help?

I have a CommandButton12 that calls CommandButton1 multiple times. Sheet2

eg

Code:
Private Sub CommandButton12_Click()
Call CommandButton1_Click
Call CommandButton1_Click
Call CommandButton1_Click
End Sub

CommandButton1 calls different Sub routines.

If I want to call CommandButton1 five times or ten times or any other amount of times, I have to go back into the code and add the required amount of calls.

Would it be possible to add a dropdown list or form on Sheet2 to choose the required amount of calls between 1 to 100 for CommandButton1.

Any help would be apprieciated

Regards

pwill
 
Last edited:

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Show us the script you have in Command Button1

You should really never have to run the same script more then once.
I'm sure we could write one script to do what you want.

If not then I will write you a script do call as many times as you want but I would still need to see your script.
 
Upvote 0
Hi My Aswer Is This, Thanks for your reply, by script do you mean the subs CommandButton1 Calls

regards
 
Upvote 0
Yes what ever the text is inside your button. This text is called your script.
 
Upvote 0
If you want to do it your way of calling the same script over and over try this:

Code:
Private Sub CommandButton2_Click()
Dim i As Long
Dim ans As Long
ans = InputBox("Enter number of times you want call to run")
    For i = 1 To ans
        Call CommandButton1_Click
    Next
End Sub
 
Upvote 0
Thanks My Aswer Is This I will give that a try.

This is the full script on sheet2 for each CommandButton with CommandButton120 triggering the others.
Would you be able to write one script for this?

Code:
Private Sub CommandButton120_Click()
Call CommandButton0_Click
End Sub

Private Sub CommandButton0_Click()

Application.ScreenUpdating = False

Call CommandButton1_Click
Call CommandButton2_Click
Call CommandButton100_Click
Call CommandButton108_Click
Call CommandButton109_Click
Call CommandButton110_Click

Application.ScreenUpdating = True

End Sub

Private Sub CommandButton100_Click()

Call CommandButton3_Click
Call CommandButton4_Click
Call CommandButton5_Click
Call CommandButton6_Click
Call CommandButton7_Click
Call CommandButton8_Click
Call CommandButton9_Click
Call CommandButton10_Click
Call CommandButton11_Click
Call CommandButton12_Click
Call CommandButton13_Click
Call CommandButton14_Click
Call CommandButton15_Click
Call CommandButton16_Click
Call CommandButton17_Click
Call CommandButton18_Click
Call CommandButton19_Click
Call CommandButton20_Click
Call CommandButton21_Click
Call CommandButton22_Click
Call CommandButton23_Click
Call CommandButton24_Click
Call CommandButton25_Click
Call CommandButton26_Click
Call CommandButton27_Click
Call CommandButton28_Click
Call CommandButton29_Click
Call CommandButton30_Click
Call CommandButton31_Click
Call CommandButton32_Click
Call CommandButton33_Click
Call CommandButton34_Click
Call CommandButton35_Click
Call CommandButton36_Click
Call CommandButton37_Click
Call CommandButton38_Click
Call CommandButton39_Click
Call CommandButton40_Click
Call CommandButton41_Click
Call CommandButton42_Click
Call CommandButton43_Click
Call CommandButton44_Click
Call CommandButton45_Click
Call CommandButton46_Click
Call CommandButton47_Click
Call CommandButton48_Click
Call CommandButton49_Click
Call CommandButton50_Click
Call CommandButton51_Click
Call CommandButton52_Click
Call CommandButton53_Click
Call CommandButton54_Click
Call CommandButton55_Click
Call CommandButton56_Click
Call CommandButton57_Click
Call CommandButton58_Click
Call CommandButton59_Click
Call CommandButton60_Click
Call CommandButton61_Click
Call CommandButton62_Click
Call CommandButton63_Click
Call CommandButton64_Click
Call CommandButton65_Click
Call CommandButton66_Click
Call CommandButton67_Click
Call CommandButton68_Click
Call CommandButton69_Click
Call CommandButton70_Click
Call CommandButton71_Click
Call CommandButton72_Click
Call CommandButton73_Click
Call CommandButton74_Click
Call CommandButton75_Click
Call CommandButton76_Click
Call CommandButton77_Click
Call CommandButton78_Click
Call CommandButton79_Click
Call CommandButton80_Click
Call CommandButton81_Click
Call CommandButton82_Click
Call CommandButton83_Click
Call CommandButton84_Click
Call CommandButton85_Click
Call CommandButton86_Click
Call CommandButton87_Click
Call CommandButton88_Click
Call CommandButton89_Click
Call CommandButton90_Click
Call CommandButton91_Click
Call CommandButton92_Click
Call CommandButton93_Click
Call CommandButton94_Click
Call CommandButton95_Click
Call CommandButton96_Click
Call CommandButton97_Click
Call CommandButton98_Click
Call CommandButton99_Click
Call CommandButton101_Click
Call CommandButton102_Click
Call CommandButton103_Click
Call CommandButton104_Click
Call CommandButton105_Click
Call CommandButton106_Click
Call CommandButton107_Click

End Sub

Regards
 
Upvote 0
I need to know what happens when you click on Commandbutton2 or CommandButton3

etc.

If you look in command button2 you should see some text which we call script.

I need to see the text in the command button.

Explain to me what your trying to do with all these buttons
 
Last edited:
Upvote 0
Hi, My Aswer Is This

Obviously CommandButton120 triggers CommandButton0 and then CommandButton0 triggers the rest.

CommandButton1 through to 110 all have different code assigned to them

eg CommandButton1 and 2 have

Code:
Private Sub CommandButton1_Click()

    Set FromSht = Sheets("Sheet1")
    Set ToSht = Sheets("Sheet2")
    
    r = 2
    
    While FromSht.Cells(r, "N") <> "" And FromSht.Cells(r, "W") <> ""
        
    r = r + 1
    
    Wend
    
    If FromSht.Cells(r, "N") <> "" Then
        
    FromSht.Cells(r, "W") = 1
        
    FromSht.Range("N" & r & ":U" & r).Copy ToSht.Range("D3:K3")
    
    End If
    
End Sub

Private Sub CommandButton2_Click()

    Set FromSht = Sheets("Sheet1")
    Set ToSht = Sheets("Sheet2")
    
    r = 2
    
    While FromSht.Cells(r, "X") <> "" And FromSht.Cells(r, "AG") <> ""
        
    r = r + 1
    
    Wend
    
    If FromSht.Cells(r, "X") <> "" Then
        
    FromSht.Cells(r, "AG") = 1
        
    FromSht.Range("X" & r & ":AE" & r).Copy ToSht.Range("D4:K4")
    
    End If
    
    Dim MyRow(98), InSht As Worksheet, OutSht As Worksheet

    Application.EnableEvents = False
    
    Set InSht = Sheets("Sheet2")
    Set OutSht = Sheets("Sheet2")
    
    MyData = InSht.Range("E5:K102")
    MyNums = InSht.Range("D5:D102")
    MyLtrs = InSht.Range("E4:K4")
    Set MyResults = OutSht.Range("AM6:AS14")
    
    For i = 1 To 98
        MyRow(i) = " " & Join(WorksheetFunction.Index(MyData, i, 0)) & " "
    CustOrd = CustOrd & MyNums(i, 1) & ","
    Next i
    
    MyResults.ClearContents
    For i = 1 To 7
        c = 0
        
    MyResults.Cells(9, i) = MyLtrs(1, i)
        For j = 1 To 98
            If InStr(MyRow(j), " " & MyLtrs(1, i) & " ") > 0 Then
                c = c + 1
                MyResults.Cells(c, i) = MyNums(j, 1)
                If c = 7 Then Exit For
            End If
        Next j
    Next i
    
    With OutSht.Sort
        
    .SortFields.Clear
        For i = 1 To 7
            .SortFields.Add Key:=MyResults.Cells(i, 1), CustomOrder:=CVar(CustOrd)
        Next i
        .SetRange MyResults
        .Orientation = xlLeftToRight
        .Apply
    End With
    
    Application.EnableEvents = True
    
End Sub

and then each CommandButton has its own code but there is a lot so I wouldn't be able to post them all right now?

does this help?

regargs
 
Upvote 0
Well in this case if all buttons have different scripts then you would need a script like I wrote.

In your original post you showed this:
Code:
[LEFT][COLOR=#333333][FONT=monospace]Private Sub CommandButton12_Click()
Call CommandButton1_Click
Call CommandButton1_Click
Call CommandButton1_Click
End Sub[/FONT][/COLOR][/LEFT]

Which is what my script does.
runs the same command button as many times as you enter into the Input Box

Your script says the same button numerous times.
It does not say commandbutton1
Then commandbutton2

So are you wanting the same command button run numerous times?
Or are you trying to have one button which the calls numerous buttons one time each?

And if so are the command buttons

1 to 200 or 2 to 300 or what.

They would need to be in order like 2 to 200
 
Upvote 0
Sorry I confused things with my first post I was trying a different approach with that. I should have put CommandButton0 not CommandButton1 and CommandButton120 not CommandButton12 sorry about that. So if you look at the full script I posted I am trying to get CommandButton120 to call CommandButton0 as many times as I choose. Which is why I was asking if I could have a dropdown box to choose from between 1 to 100 then I could choose to click CommandButton0 through CommandButton120 as many times as I like. Hope that makes sense?

I haven't tried the code you gave but will give it a try if you think thats the best option?

many thanks much apprieciated

regards
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top