Separation by state

N

ngore

New Member
Joined
Aug 19, 2014
Messages
4
I apologize if this has already been answered or in the wrong section. I have looked for the answer in many places on the internet, including this website, and haven't found my answer.

The intent of the macro is to create a form that will enter in Item details and the state it will be shipped to. Categorizing the items by the state they are being shipped to. All states except Hawaii and Alaska.

I have a UserForm that collects data using textboxes. Textboxes 1-5 corresponding with the next empty cell and columns A - E.

TextBox4 is the state acronym. Based on the state entered, I would like it to select the corresponding sheet, then enter the data into the next empty row on that sheet.

example: TextBox4 = AL. I want the sheet associated with Alabama to be selected then if row 2 is empty, textboxes 1, 2, 3, and 5 will be inserted into the cells.

My Problem with the code is selecting the correct sheet (all renamed to the state they correspond with) and then selecting the correct cells. Any suggestions??

I know that I need a cell address in the "If block" in my code but I'm not sure how to select the last row. I attempted to use this:

Code: 
[COLOR=#00356A][FONT=Arial]LastRow = Range("A65536").End(xlUp).Row[/FONT][/COLOR]



Here is my code:

Code: 
Private Sub CommandButton1_Click()


Dim iRow As Long
Dim LastRow As Object
Dim ws As Worksheet

[COLOR=#00356A][FONT=Arial]LastRow = Range("A65536").End(xlUp).Row[/FONT][/COLOR]


If Me.TextBox1 = "" Or Me.TextBox2 = "" _
    Or Me.TextBox3 = "" Or Me.TextBox4 = "" _
    Then MsgBox ("All Fields Must be Completed")


If TextBox4.Value = "AL" Then
    Sheets("AL").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "AZ" Then
    Sheets("AZ").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "AR" Then
    Sheets("AR").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "CA" Then
    Sheets("CA").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "CO" Then
    Sheets("CO").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "CT" Then
    Sheets("CT").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "DE" Then
    Sheets("DE").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "DC" Then
    Sheets("DC").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "FL" Then
    Sheets("FL").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "GA" Then
    Sheets("GA").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "ID" Then
    Sheets("ID").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "IN" Then
    Sheets("IN").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "IA" Then
    Sheets("IA").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "KS" Then
    Sheets("KS").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "KY" Then
    Sheets("KY").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "LA" Then
    Sheets("LA").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "ME" Then
    Sheets("ME").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "MD" Then
    Sheets("MD").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "MA" Then
    Sheets("MA").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "MI" Then
    Sheets("ME").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "MN" Then
    Sheets("MN").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "MS" Then
    Sheets("MS").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "MO" Then
    Sheets("MO").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "MT" Then
    Sheets("MT").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "NE" Then
    Sheets("NE").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "NV" Then
    Sheets("NV").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "NH" Then
    Sheets("NH").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "NJ" Then
    Sheets("NJ").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "NM" Then
    Sheets("NM").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "NY" Then
    Sheets("NY").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "NC" Then
    Sheets("NC").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "ND" Then
    Sheets("ND").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "OH" Then
    Sheets("OH").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "OK" Then
    Sheets("OK").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "OR" Then
    Sheets("OR").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "PA" Then
    Sheets("PA").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "RI" Then
    Sheets("RI").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "SC" Then
    Sheets("SC").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "SD" Then
    Sheets("SD").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "TN" Then
    Sheets("TN").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "TX" Then
    Sheets("TX").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "UT" Then
    Sheets("UT").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "VT" Then
    Sheets("VT").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "VA" Then
    Sheets("VA").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "WA" Then
    Sheets("WA").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "WV" Then
    Sheets("WV").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "WI" Then
    Sheets("WI").Cells().Value = TextBox1.Text
ElseIf TextBox4.Value = "WY" Then
    Sheets("WY").Cells().Value = TextBox1.Text
Else: MsgBox "Enter a valid state"


End If


iRow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row


With ws
    LastRow.Offset(iRow, 1).Value = TextBox1.Value
    LastRow.Offset(iRow, 2).Value = TextBox2.Value
    LastRow.Offset(iRow, 3).Value = TextBox3.Value
    LastRow.Offset(iRow, 4).Value = TextBox4.Value
    LastRow.Offset(iRow, 5).Value = TextBox5.Value
End With


Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""


End Sub

Thank in advance for your help!
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
Try

Rich (BB code): 
Dim iRow As Long
Dim LastRow As Long
Dim ws As Worksheet

LastRow = Range("A" & Rows.Count).End(xlUp).Row
 
Upvote 0
I would suggest that you replace TextBox4 with a combobox and do something like the following:

Code: 
Private Sub ComboBox1_Change()
Dim NewState As String
If ComboBox1.Text = "Enter a New State" Then
    'You will need to create a new sheet here as it doesn't exist.
    'I would suggest you hold a template sheet within the workbook and hide it.
Else
    Sheets(ComboBox1.Text).Select
End If
End Sub


Private Sub UserForm_Initialize()
Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Sheets
        If ws.Visible = xlSheetVisible Then ComboBox1.AddItem ws.Name
    Next ws
ComboBox1.AddItem "Enter a New State", 0
End Sub

Then you add a button to your form that will add the values from textboxes 1, 2, 3 & 5 once the user clicks it.
 
Upvote 0
Thank you very much! The code worked wonderfully and the suggestion was very helpful!

Are there any suggestions on how to shorten the code for each if?

Instead of this for each state:

Code: 
Dim LastRow As Long


LastRow = Range("A" & Rows.Count).End(xlUp).Row

If ComboBox1.Value = "AL" Then
    Sheets("AL").Cells(LastRow + 1).Value = TextBox1.Value
    Sheets("AL").Cells(LastRow + 2).Value = TextBox2.Value
    Sheets("AL").Cells(LastRow + 3).Value = TextBox3.Value
    Sheets("AL").Cells(LastRow + 4).Value = ComboBox1.Value
    Sheets("AL").Cells(LastRow + 5).Value = TextBox5.Value

I was thinking of a for loop to increment "LastRow" but how would I change the TextBox value that corresponds with it?
 
Last edited:
Upvote 0
If you are using the code I suggested then you don't need to use:

If Combobox1.Value - "AL" etc etc

As the relevant worksheet will already be active.

You just need to find the last row of the active sheet.


Post all of you code again so we can see what you have.
 
Upvote 0
Code: 
Private Sub CommandButton2_Click()

Me.Hide

End Sub

Code: 
Private Sub UserForm_Initialize()


Dim ws As Worksheet
    
For Each ws In ActiveWorkbook.Sheets
    If ws.Visible = xlSheetVisible Then ComboBox1.AddItem ws.Name
Next ws


ComboBox1.RemoveItem (0)
Me.ComboBox1.Value = "Select A State"

End Sub

I remove item 0 because I want the first worksheet visible

Code: 
Private Sub CommandButton1_Click()


Dim LastRow As Long
Dim ws As Worksheet


LastRow = Range("A" & Rows.Count).End(xlUp).Row
MsgBox LastRow


If Me.TextBox1 = "" Or Me.TextBox2 = "" _
    Or Me.TextBox3 = "" Or Me.ComboBox1 = "" _
    Then MsgBox ("All Fields Must be Completed")


If ComboBox1.Value = "AL" Then
    Sheets("AL").Cells(LastRow + 1).Value = TextBox1.Value
    Sheets("AL").Cells(LastRow + 2).Value = TextBox2.Value
    Sheets("AL").Cells(LastRow + 3).Value = TextBox3.Value
    Sheets("AL").Cells(LastRow + 4).Value = TextBox5.Value
ElseIf ComboBox1.Value = "AZ" Then
    Sheets("AZ").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "AR" Then
    Sheets("AR").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "CA" Then
    Sheets("CA").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "CO" Then
    Sheets("CO").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "CT" Then
    Sheets("CT").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "DE" Then
    Sheets("DE").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "DC" Then
    Sheets("DC").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "FL" Then
    Sheets("FL").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "GA" Then
    Sheets("GA").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "ID" Then
    Sheets("ID").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "IN" Then
    Sheets("IN").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "IA" Then
    Sheets("IA").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "KS" Then
    Sheets("KS").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "KY" Then
    Sheets("KY").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "LA" Then
    Sheets("LA").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "ME" Then
    Sheets("ME").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "MD" Then
    Sheets("MD").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "MA" Then
    Sheets("MA").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "MI" Then
    Sheets("ME").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "MN" Then
    Sheets("MN").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "MS" Then
    Sheets("MS").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "MO" Then
    Sheets("MO").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "MT" Then
    Sheets("MT").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "NE" Then
    Sheets("NE").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "NV" Then
    Sheets("NV").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "NH" Then
    Sheets("NH").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "NJ" Then
    Sheets("NJ").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "NM" Then
    Sheets("NM").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "NY" Then
    Sheets("NY").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "NC" Then
    Sheets("NC").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "ND" Then
    Sheets("ND").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "OH" Then
    Sheets("OH").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "OK" Then
    Sheets("OK").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "OR" Then
    Sheets("OR").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "PA" Then
    Sheets("PA").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "RI" Then
    Sheets("RI").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "SC" Then
    Sheets("SC").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "SD" Then
    Sheets("SD").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "TN" Then
    Sheets("TN").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "TX" Then
    Sheets("TX").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "UT" Then
    Sheets("UT").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "VT" Then
    Sheets("VT").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "VA" Then
    Sheets("VA").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "WA" Then
    Sheets("WA").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "WV" Then
    Sheets("WV").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "WI" Then
    Sheets("WI").Cells(LastRow).Value = TextBox1.Value
ElseIf ComboBox1.Value = "WY" Then
    Sheets("WY").Cells(LastRow).Value = TextBox1.Value
Else: MsgBox "Select A State"


End If


'Me.TextBox1.Value = ""
'Me.TextBox2.Value = ""
'Me.TextBox3.Value = ""
'Me.ComboBox1.Value = "Select A State"
'Me.TextBox5.Value = ""


End Sub

I am working on the code for putting the correct data into each field. When i use LastRow, it stays in the current row. when i add +1, +2, etc. it moves from column A to column B, not from Row 1 to Row 2. I do no want to have to add any additional math to the last row.

TextBox1 should correspond with column A, TextBox2 with B, TextBox3 with C, TextBox5 with D.

Since I only have headers on each sheet right now, the next empty row should be 2 but it keeps inputting data over my headers, but leaves A1 because of the math I added.
 
Last edited:
Upvote 0
Firstly you need the following for when a user Selects combobox1:

Code: 
Private Sub ComboBox1_Change()
Dim NewState As String
If ComboBox1.Text = "Enter a New State" Then
    'You will need to create a new sheet here as it doesn't exist.
    'I would suggest you hold a template sheet within the workbook and hide it.
Else
    Sheets(ComboBox1.Text).Select
End If
End Sub

This will make the State they select the ActiveSheet.

This will also allow us to code for the eventuality that they wish to enter data for a state that does not exist in your workbook.

Your CommandButton1 code would then look like this:

Code: 
Private Sub CommandButton1_Click()


Dim LastRow As Long
Dim ws As Worksheet


Set ws = ActiveSheet


LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row


If Me.TextBox1 = "" Or Me.TextBox2 = "" _
    Or Me.TextBox3 = "" Or Me.ComboBox1 = "" _
    Then MsgBox "All Fields Must be Completed": Exit Sub


    ws.Cells(LastRow + 1, 1).Value = TextBox1.Value
    ws.Cells(LastRow + 1, 2).Value = TextBox2.Value
    ws.Cells(LastRow + 1, 3).Value = TextBox3.Value
    ws.Cells(LastRow + 1, 4).Value = TextBox5.Value


End Sub

One thing to note though, I tend to use the following to find the lastrow. But yours will suffice if you are sure no data can get into rows B - D without A being filled.
LastRow = Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row

 
Last edited:
Upvote 0
The template I am creating will have all of the sheets with their headers created already. At first I missed that the ComboBox would select the active sheet. (I'm rather new to coding)

Thank you for your help and suggestions!
I appreciate it greatly!
 
Upvote 0
ngore said:
The template I am creating will have all of the sheets with their headers created already. At first I missed that the ComboBox would select the active sheet. (I'm rather new to coding)

Thank you for your help and suggestions!
I appreciate it greatly!
Click to expand...

You're Welcome!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Populating a ListBox with Columns Using Pre-Filled Array
Replies
5
Views
389
Legacy 143009
L
ivonsurf123
  • Question
Update cells and highlight changes based on criteria
Replies
1
Views
211
ivonsurf123
ivonsurf123
N
  • Question
User Form data entry as date and number!!!
Replies
4
Views
506
Akuini
Akuini
W
  • Question
Specific value from excel sheet on to the userform.
Replies
3
Views
992
Windy borough
W
W
  • Question
Excel 2016 VBA, How to transfer specific data on to another sheet in same workbook?
Replies
3
Views
465
Fluff
Fluff

Forum statistics

Threads
1,215,004
Messages
6,122,656
Members
449,091
Latest member
peppernaut

Share this page

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
Back
Top