PDA

View Full Version : Solved: Selecting an Empty Row and range naming the selected cells



Rlb53
08-28-2011, 01:18 PM
I'm certain that I'm "re-inventing the Wheel" here... but I'm attempting to configure a vendor database that is capable of being populated, searched and amended through userforms.

In the code below, I'm searching for the last empty row in a database.
At the time the data is transferred to the cells, I'd like to Range.Name the location to simplify the return of associated values in the textbox's incorporated into the userform used for viewing and modification.

The intended "Range Name" is the name of the vendor.
ie: " Selected Plumber, Inc." which would be entered into Textbox1 along with additional corresponding data... address... phone...etc. in seperate textboxes.

The first hurdle is to configure the range name so that it is valid.
"Selected_Plumber_Inc". The spaces would need to be filled and the comma disposed of. (I may be trying to make it too easy on the data entry personnel here, but I could likely generate a popup instructing them of how to format the name entry and overcome this issue)

The second hurdle is to configure VBA to identify the range in which the data is placed and name it accordingly.

As always !... You're assistance is greatly appreciated!!




Private Sub Add_Vendor_Info_To_Database_Click()
Dim emptyRow As Long
Application.ScreenUpdating = False
Sheets("Suppliers").Activate
ActiveSheet.Unprotect
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
ActiveSheet.Unprotect

'Export Data to worksheet
Cells(emptyRow, 1).Value = Textbox1.Value
Cells(emptyRow, 2).Value = TextBox2.Value
Cells(emptyRow, 3).Value = TextBox3.Value
Cells(emptyRow, 4).Value = TextBox5.Value
Cells(emptyRow, 5).Value = TextBox4.Value
Cells(emptyRow, 6).Value = TextBox6.Value
Cells(emptyRow, 7).Value = TextBox7.Value
Cells(emptyRow, 8).Value = TextBox12.Value
Cells(emptyRow, 9).Value = TextBox14.Value
Cells(emptyRow, 10).Value = TextBox13.Value
Cells(emptyRow, 11).Value = TextBox9.Value
Cells(emptyRow, 12).Value = TextBox10.Value
Cells(emptyRow, 13).Value = TextBox11.Value

'This is where I get the error
Range(Cells(emptyRow, "1:13")).Name = Textbox1.Value


ActiveSheet.Protect
Unload Me
' Sheets("main controls").Select
End Sub

Rlb53
08-28-2011, 02:04 PM
After taking the time to post the thread and request assistance.... the solution came to me just moments afterwards.

Thanks again for being here !