adjusting code copy data from userform to specific sheets using array

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
977
Office Version
  1. 2010
hello
I have this code works well but if anybody help exchange this code with another code using array
I have many combobox and textbox it takes more time if I write each active x (combobox & textbox)
so what i want when get in array about combobox & textbox like this

Code:
' 3 combobox  in array
for i = 1 to 3
'array(1,2,3) it means copying to column a,b,c as in my code
("combobox" & i) = array(1,2,3)
'3 textbox in array
for s=1 to 3
'array(4,5,6)it means copying to column d,e,f as in my code
("textbox" & s) =array(4,5,6)
this is my code
VBA Code:
Private Sub CommandButton1_Click()
Dim lr1, lr2 As Integer
Dim wk1, wk2 As Worksheet
Set wk1 = Sheets("expirity")
Set wk2 = Sheets("NEW")
lr1 = wk1.Range("a" & Rows.Count).End(xlUp).Row + 1
lr2 = wk2.Range("a" & Rows.Count).End(xlUp).Row + 1
If OptionButton1.Value = True Then
With wk1
.Range("a" & lr1) = Me.ComboBox1.Value
.Range("b" & lr1) = Me.ComboBox2.Value
.Range("c" & lr1) = Me.ComboBox3.Value
.Range("d" & lr1) = Me.TextBox1.Value
.Range("e" & lr1) = Me.TextBox2.Value
.Range("f" & lr1) = Me.TextBox3.Value

End With
Else
With wk2
.Range("a" & lr2) = Me.ComboBox1.Value
.Range("b" & lr2) = Me.ComboBox2.Value
.Range("c" & lr2) = Me.ComboBox3.Value
.Range("d" & lr2) = Me.TextBox1.Value
.Range("e" & lr2) = Me.TextBox2.Value
.Range("f" & lr2) = Me.TextBox3.Value

End With
End If
Me.ComboBox1.Value = ""
 Me.ComboBox2.Value = ""
 Me.ComboBox3.Value = ""
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
End Sub

thanks in advance
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,088
Office Version
  1. 2013
Platform
  1. Windows
What about
VBA Code:
Private Sub CommandButton1_Click()
    Dim lr1, lr2 As Integer
    Dim wk1, wk2 As Worksheet
    Set wk1 = Sheets("expirity")
    Set wk2 = Sheets("NEW")
    lr1 = wk1.Range("a" & Rows.Count).End(xlUp).Row + 1
    lr2 = wk2.Range("a" & Rows.Count).End(xlUp).Row + 1
    If OptionButton1.Value = True Then
        With wk1
        For i = 1 To 3
        .Cells(lr, i) = Me.Controls("combobox" & i).Value
        .Cells(lr, i + 3) = Me.Controls("TextBox" & i).Value
        Next

        End With
    Else
        With wk2
        For i = 1 To 3
        .Cells(lr, i) = Me.Controls("combobox" & i).Value
        .Cells(lr, i + 3) = Me.Controls("TextBox" & i).Value
        Next

        End With
    End If
    For i = 1 To 3
        Me.Controls("combobox" & i).Value = ""
        Me.Controls("TextBox" & i).Value = ""
    Next
   
End Sub
 
Solution

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
977
Office Version
  1. 2010
great work ! you have error i know this is typo should use lr1,lr2 not lr
should change this
VBA Code:
.Cells(lr, i) = Me.Controls("combobox" & i).Value
        .Cells(lr, i + 3) = Me.Controls("TextBox" & i).Value
to this
VBA Code:
.Cells(lr1, i) = Me.Controls("combobox" & i).Value
        .Cells(lr1, i + 3) = Me.Controls("TextBox" & i).Value
the same thing with wk2
i have a question it's curiosity if copy combobox & textbox to non adjacent columns for in stance
combobox1 copy to column b
combobox2 copy to column d
combobox3 copy to column f
textbox 1 copy to column a
textbox2 copy to column c
textbox 3 copy to column e
so how the code is it ?
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,088
Office Version
  1. 2013
Platform
  1. Windows
Maybe some thing like
VBA Code:
With wk2
            For i = 1 To 6 Step 2
                .Cells(lr2, i) = Me.Controls("combobox" & i).Value
                .Cells(lr2, i + 1) = Me.Controls("TextBox" & i).Value
            Next

        End With
 

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
977
Office Version
  1. 2010

ADVERTISEMENT

unfortunately gives error so maybe somebody else has idea help me about my question , any way thanks for solving my original post
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,088
Office Version
  1. 2013
Platform
  1. Windows
My mistak
VBA Code:
With wk2
    c = 1
    For i = 1 To 6 Step 2
        .Cells(lr2, i) = Me.Controls("combobox" & c).Value
        .Cells(lr2, i + 1) = Me.Controls("TextBox" & c).Value
        c = c + 1
    Next

End With
 

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
977
Office Version
  1. 2010
should define variable c it shows not defined , I declare it as integer
it works perfectly thanks very much
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,088
Office Version
  1. 2013
Platform
  1. Windows
You are welcome
And thank you for the feedback
Be happy
 

Watch MrExcel Video

Forum statistics

Threads
1,127,000
Messages
5,622,122
Members
415,878
Latest member
jjj12345

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
Top