Code to input multiple comboxes and Textboxes into specific Rows & Columns

[ccsher77]

Hi Everyone,

I am trying to input data from a user form into specific cells.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Basically I have a userform with 2 comboboxes and a text box.<o></o>
<o></o>
Combobox 1 lists months (Jan to Dec) and i want this to set the row for the data from the textbox to be input in to.<o></o>
<o></o>
Combobox 2 lists B,C,D,E this will set which column to drop the data from the text box <o></o>
into.<o></o>
<o></o>
So for example the operator would select Jan and B from the comboboxes which would place the data into cell B1 (Jan = Row 1).<o></o>
<o></o>
I have come with a very ugly and longwinded way of doing this using a series of IF statements e.g (If cboMonth = ("Jan") Then Cell("A1").Select) which will work for my purposes but may take m a while to code.<o></o>
<o></o>
Is there a simpler way?<o></o>
<o></o>
Any help would be gratefully received.<o></o>
<o></o>
Best Regards,<o></o>
<o></o>
Craig

 

[Domenic]

Try...

[font=Verdana]r = Me.ComboBox1.ListIndex + 1  [color=green]'months[/color]

c = Me.ComboBox2.Value   [color=green]'columns[/color]

Cells(r, c).Value = Me.TextBox1.Value[/font]

Change the name of the controls, accordingly.

 

[ccsher77]

Hi Thanks for your Quick response,

I Have tried Adding your code as Below:

Private Sub CommandButton1_Click()
Sheets("Sheet3").Activate
r = Me.ComboBox1.ListIndex + 1
C = Me.ComboBox2.Value
Cells(r, C).Value = Me.TextBox1.Value
End Sub
Private Sub UserForm_Initialize()
Me.ComboBox1.List = Worksheets("Sheet1").Range("A2:A13").Value
Me.ComboBox2.List = Worksheets("Sheet1").Range("B2:E2").Value
End Sub

But when I run the macro I get the following error:
Runtime Error '1004':
Application-defined or Object-defined error

The error appear in the following line of the code:

Cells(r, C).Value = Me.TextBox1.Value


Also I have noticed that although the first combobox is populating from the Range A2:A13 Combobox 2 is only picking up one of the column headings is there adifferent comand to specify a range of column headings?

I am extremley new to this so apologies if this is a basic error, but I hope to learn more in time.

Thanks
Craig

 

[Domenic]

Try...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] CommandButton1_Click()
    [color=darkblue]Dim[/color] r [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] c [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]If[/color] Me.ComboBox1.Value = "" [color=darkblue]Then[/color]
        MsgBox "Please select a month...", vbExclamation
        [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]If[/color] Me.ComboBox2.Value = "" [color=darkblue]Then[/color]
        MsgBox "Please select a column...", vbExclamation
        [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    If Me.TextBox1.Value = "" [color=darkblue]Then[/color]
        MsgBox "Please enter a value in the text box...", vbExclamation
        [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]End[/color] If
    Sheets("Sheet3").Activate
    r = Me.ComboBox1.ListIndex + 1
    c = Me.ComboBox2.Value
    Cells(r, c).Value = Me.TextBox1.Value
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] UserForm_Initialize()
    Me.ComboBox1.List = Worksheets("Sheet1").Range("A2:A13").Value
    Me.ComboBox2.List = WorksheetFunction.Transpose(Worksheets("Sheet1").Range("B2:E2").Value)
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

Also, if you do not want to activate Sheet3 when the command button is pressed, replace...

[font=Verdana]    Sheets("Sheet3").Activate
    r = Me.ComboBox1.ListIndex + 1
    c = Me.ComboBox2.Value
    Cells(r, c).Value = Me.TextBox1.Value
[/font]

with

[font=Verdana]    r = Me.ComboBox1.ListIndex + 1
    c = Me.ComboBox2.Value
    Sheets("Sheet3").Cells(r, c).Value = Me.TextBox1.Value
[/font]