Forum Discussion

JT_2021's avatar
JT_2021
Copper Contributor
Aug 08, 2021

VBA code Two buttons merge

I need to merge these two buttons into one, i am very new to this and keep breaking the program 😉


Private Sub cmdAdd_Click()
'dimension the variable
Dim addme As Range, cNum As Integer
Dim x As Integer, y As Integer, Ck As Integer
'set variables
Set addme = Sheet4.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)
cNum = 19
Ck = 0
'run the for loop
For x = 0 To Me.lstMulti.ListCount - 1
'add condition statement
If Me.lstMulti.Selected(x) Then
Ck = 1
'second loop
For y = 0 To cNum
addme.Offset(0, y) = Me.lstMulti.List(x, y)
Next y
Set addme = addme.Offset(1, 0)

End If
'clear the selected values
lstMulti.Selected(x) = False
Next x
'send a message if nothing is selected


If Ck = 0 Then
MsgBox "There is nothing selected"

End If
End Sub

 


Private Sub CommandButton1_Click()
'dimension the variable
Dim addme As Range
Dim x As Integer, Ck As Integer
'set variables
Set addme = Sheet3.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)
Ck = 0
'run the for loop
For x = 0 To lstMulti.ListCount - 1
'add condition statement
If Me.lstMulti.Selected(x) Then
Ck = 1
addme = Me.lstMulti.List(x)

addme.Offset(0, 1) = Me.lstMulti.List(x, 3)
addme.Offset(0, 2) = Me.lstMulti.List(x, 1)
addme.Offset(0, 3) = Me.lstMulti.List(x, 14)
addme.Offset(0, 4) = Me.lstMulti.List(x, 15)
addme.Offset(0, 5) = Me.lstMulti.List(x, 16)
addme.Offset(0, 5) = Me.lstMulti.List(x, 17)


Set addme = addme.Offset(1, 0)
'clear the selected row
lstMulti.Selected(x) = False
End If
Next x
'send a message if nothing is selected
If Ck = 0 Then
MsgBox "There is nothing selected"
End If
End Sub

excel
Macros and VBA
office 365

2 Replies

Sort By
  • Vedran_Loani's avatar
    Vedran_Loani
    Copper Contributor
    Aug 08, 2021

    Call one of the buttons from the other one, you can hide one of these buttons as well, check this for more details:

    https://martinbosanacvba.blogspot.com/2021/08/combining-two-buttons-into-one-in-excel.html 

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Aug 08, 2021

    JT_2021 

    Try this:

    Private Sub cmdAdd_Click()
    'dimension the variable
    Dim addme1 As Range, addme2 As Range, cNum As Integer
    Dim x As Integer, y As Integer, Ck As Boolean
    
    'set variables
    Set addme1 = Sheet4.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)
    Set addme2 = Sheet3.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)
    cNum = 19
    
    'run the for loop
    For x = 0 To Me.lstMulti.ListCount - 1
        'add condition statement
        If Me.lstMulti.Selected(x) Then
            Ck = True
            'handle addme1
            For y = 0 To cNum
                addme1.Offset(0, y) = Me.lstMulti.List(x, y)
            Next y
            Set addme1 = addme1.Offset(1, 0)
            'handle addme2
            addme2 = Me.lstMulti.List(x)
            addme2.Offset(0, 1) = Me.lstMulti.List(x, 3)
            addme2.Offset(0, 2) = Me.lstMulti.List(x, 1)
            addme2.Offset(0, 3) = Me.lstMulti.List(x, 14)
            addme2.Offset(0, 4) = Me.lstMulti.List(x, 15)
            addme2.Offset(0, 5) = Me.lstMulti.List(x, 16)
            addme2.Offset(0, 5) = Me.lstMulti.List(x, 17)
            Set addme2 = addme2.Offset(1, 0)
            'clear the selected row
            lstMulti.Selected(x) = False
        End If
    Next x
    
    'send a message if nothing is selected
    If Not Ck Then
        MsgBox "There is nothing selected"
    End If
End Sub

Resources