Inserting data within a named range..HELP

sulrich

New Member
Joined
Feb 15, 2005
Messages
20
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... :cry: :cry:
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi

add

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

after your code

hope this helps
jindon
 

sulrich

New Member
Joined
Feb 15, 2005
Messages
20
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.
:confused:
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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
Code:
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

New Member
Joined
Feb 15, 2005
Messages
20

ADVERTISEMENT

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

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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

New Member
Joined
Feb 15, 2005
Messages
20

ADVERTISEMENT

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. :biggrin:
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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

New Member
Joined
Feb 15, 2005
Messages
20
Hi Jindon...
I'm still getting this "application defined error". Any ideas on what's going on?? :cry:
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,228
Messages
5,594,934
Members
413,953
Latest member
Arthur1471

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