Inserting data within a named range..HELP

[sulrich]

I'm not an expert in vba in excel, however I would like some help. I have a worksheet with a named range called "Data". And, also on the worksheet I have a command button that initiates a userform. On that userform I have TextBox1.Value and TextBox2.Value...after the OK button is clicked within the userform, it inserts the data from the userform in the last row of that named range. I have part of the code to that adds the data to the worksheet, but the named range area stays the same. How do I get this to work. This is what I have so far:

Sheets("Sheet1").Range("A65536").End(xlUp)(2, 1).Value = TextBox1.Value
Sheets("Sheet1").Range("A65536").End(xlUp)(1, 2).Value = TextBox2.Value
Sheets("Sheet1").Range("A65536").End(xlUp)(1, 3).Value = TextBox3.Value

Thanks in advance...

 

[jindon]

Hi

add

Sheets("sheet1").Range("a1", Range("c65536").End(xlUp)).Name = "data"

after your code

hope this helps
jindon

 

[sulrich]

One more question....If I have 2 named ranges(let's say "data" and "data1"), and depending on what the user inputs in TextBox1.Value determines on what named range the data gets inserted into. For example, if TextBox1.Value = "Blue", information from TextBox1, TextBox2 and TextBox3 will be inserted into the named range "Data". However, the data is being inserted onto the worksheet, but how do I code it to insert into a specified named range?
The range for Data1 is =Sheet1!$A$1:$E$10 and Data2 is =Sheet1!$A$12:$E$19.

Thank you inadvance.

 

[jindon]

Hi,
I don't think the position of two tables are practical, because
if you add one data to table data, there will be no room to add more.

Anyway, add command button on your form and pastethe code like this

Private Sub CommandButton1_Click()
Dim ws As Worksheet, LastR As Long, LastR1 As Long, x As Long
Set ws = Sheets("sheet1")
With ws
    LastR = .Range("a1").End(xlDown).Row
    LastR1 = .Range("a12").End(xlDown).Row
    If Me.TextBox1 = "Blue" Then
        x = LastR
    Else
        x = LastR1
    End If
    For i = 1 To 3
        .Cells(x + 1, i).Value = Me.Controls(i)
    Next
    Set ws = Nothing
End With
End Sub

hope this helps
jindon

 

[sulrich]

Thanks Jindon... Can you explain to me "Me.Controls"....I know I'm missing an object.

 

[jindon]

sulrich,

Me.Controls refers to all the controls on the form and i is an index number.

according to your post
changing

Me.Controls("Textbox" & i)

would be better

rgds,
jindon

 

[sulrich]

Jindon..
I'm getting the following error: application defined or object defined error at ws.Cells(x + 1, i).Value = Me.Controls("Textbox" & i). How do I fix this?

Thank you for all your help.

 

[jindon]

sulrich,

It should work if the textbox object names are like you difined
i,e, Textbox1, Textbox2 and Textbox3

change:
For i = 1 To 3
.Cells(x + 1, i).Value = Me.Controls(i)
Next

to:
.Cells(x + 1, i).Value = Me.Textbox1
.Cells(x + 1, i).Value = Me.Textbox2
.Cells(x + 1, i).Value = Me.Textbox3

and make sure the name of the sheet is correct.

rgds,
jindon

 

[sulrich]

Hi Jindon...
I'm still getting this "application defined error". Any ideas on what's going on??

 

[jindon]

Hi,

Let me cralify the situation.

1) you created userform.
2) your userform has 3 textboxes and at least one command button.
3) control name for the textbox are , textbox1, textbox2 and textbox3
4) you have pasted the code onto form module.

correct?

jindon