Copy multiple activex control values to new ws

Chickenmanc

New Member
Joined
Mar 26, 2019
Messages
8
I am learning vba on my own and I found get helpful info from this forum so I joined. Thanks. I am trying to take 4 different combobox values, and 2 textbox values from one sheet and paste them in first empty row of another worksheet.
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Welcome to the forum

Try this:

Code:
Sub Copy_multiple_activex()
    Dim sh1 As Object, sh2 As Worksheet
    Set sh1 = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")  'Source
    Set sh2 = Sheets("[COLOR=#ff0000]Sheet2[/COLOR]")  'Destination
    
    If sh1.ComboBox1.Value = "" Then
        MsgBox "Fill combo 1"
        Exit Sub
    End If
    lr = sh2.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
    sh2.Cells(lr, "A").Value = sh1.ComboBox1.Value
    sh2.Cells(lr, "B").Value = sh1.ComboBox2.Value
    sh2.Cells(lr, "C").Value = sh1.ComboBox3.Value
    sh2.Cells(lr, "D").Value = sh1.ComboBox4.Value
    sh2.Cells(lr, "E").Value = sh1.TextBox1.Value
    sh2.Cells(lr, "F").Value = sh1.TextBox2.Value
End Sub
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
change this
Code:
Set sh1 = Sheets("Sheet1")  'Source

For this
Code:
Set sh1 = Activesheet  'Source
 

Chickenmanc

New Member
Joined
Mar 26, 2019
Messages
8

ADVERTISEMENT

Thanks. I appreciate it. I feel like I am learning things but when you see different ways to perform the same code it gets confusing.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
You're welcome, thanks for the feedback.
 

Chickenmanc

New Member
Joined
Mar 26, 2019
Messages
8

ADVERTISEMENT

I added a label as well. I used your code

Code:
ub Copy_multiple_activex()
    Dim sh1 As Object, sh2 As Worksheet
    Set sh1 = Sheets("Sheet1")  'Source
    Set sh2 = Sheets("Sheet2")  'Destination
    
    If sh1.ComboBox1.Value = "" Then
        MsgBox "Fill combo 1"
        Exit Sub
    End If
    lr = sh2.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
    sh2.Cells(lr, "A").Value = sh1.ComboBox1.Value
    sh2.Cells(lr, "B").Value = sh1.ComboBox2.Value
    sh2.Cells(lr, "C").Value = sh1.ComboBox3.Value
    sh2.Cells(lr, "D").Value = sh1.ComboBox4.Value
    sh2.Cells(lr, "E").Value = sh1.TextBox1.Value
    sh2.Cells(lr, "F").Value = sh1.TextBox2.Value
End Sub
[code]

I tried:
sh2.cells(lr, ”G”).value = sh1.cartnum.caption
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Do you have a problem with that?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,708
Messages
5,524,431
Members
409,577
Latest member
Dwg

This Week's Hot Topics

Top