Multiple Userform One works the other not

[PuntingJawa]

I am having trouble with one of my userforms. I have set up a simplistic input for Receiving and have a nearly identical one for shipping. The receiving works fine (See first) but shipping will not work on it's page. Is there something I must change when using multiple userforms in the same workbook?

Working (Receiving)

Private Sub TextBox1_Change()

End Sub

Private Sub TextBox10_Change()
    TextBox10.Text = UCase(TextBox10.Text)
End Sub

Private Sub TextBox11_Change()
    TextBox11.Text = UCase(TextBox11.Text)
End Sub

Private Sub TextBox2_Change()
    TextBox2.Text = UCase(TextBox2.Text)
End Sub

Private Sub TextBox3_Change()
    TextBox3.Text = UCase(TextBox3.Text)
End Sub

Private Sub TextBox4_Change()
    TextBox4.Text = UCase(TextBox4.Text)
End Sub

Private Sub TextBox5_Change()
    TextBox5.Text = UCase(TextBox5.Text)
End Sub

Private Sub TextBox6_Change()
    TextBox6.Text = UCase(TextBox6.Text)
End Sub

Private Sub TextBox7_Change()
    TextBox7.Text = UCase(TextBox7.Text)
End Sub

Private Sub TextBox8_Change()
    TextBox8.Text = UCase(TextBox8.Text)
End Sub

Private Sub TextBox9_Change()
    TextBox9.Text = UCase(TextBox9.Text)
End Sub

Private Sub UserForm_Activate()

TextBox1.Text = Format(Now(), "MM/DD/YY")


End Sub
Private Sub CommandButton1_Click()

erow = Sheets("Corp Receiving").Range("a" & Rows.Count).End(xlUp).Row
    Range("A" & erow + 1) = TextBox1.Value
    Range("B" & erow + 1) = TextBox2.Value
    Range("C" & erow + 1) = TextBox3.Value
    Range("D" & erow + 1) = TextBox4.Value
    Range("E" & erow + 1) = TextBox5.Value
    Range("F" & erow + 1) = TextBox6.Value
    Range("G" & erow + 1) = TextBox7.Value
    Range("I" & erow + 1) = "PS " & TextBox8.Value
    Range("J" & erow + 1) = "PO " & TextBox9.Value
    Range("K" & erow + 1) = TextBox10.Value
    Range("L" & erow + 1) = TextBox11.Value

End Sub

Not working (Shipping)

Private Sub TextBoxs1_Change() 'Date box

End Sub

Private Sub TextBoxs10_Change()
    TextBoxs10.Text = UCase(TextBoxs10.Text) 'Our RMA box
End Sub

Private Sub TextBoxs11_Change()
    TextBoxs11.Text = UCase(TextBoxs11.Text) 'Customer RMA box
End Sub

Private Sub TextBoxs2_Change()
    TextBoxs2.Text = UCase(TextBoxs2.Text) 'Customer Name box
End Sub

Private Sub TextBoxs3_Change()
    TextBoxs3.Text = UCase(TextBoxs3.Text) 'Project Code box
End Sub

Private Sub TextBoxs4_Change()
    TextBoxs4.Text = UCase(TextBoxs4.Text) 'Packing # or Item #'s and QTYs box
End Sub

Private Sub TextBoxs5_Change()
    TextBoxs5.Text = UCase(TextBoxs5.Text) 'Carrier company box
End Sub

Private Sub TextBoxs6_Change()
    TextBoxs6.Text = UCase(TextBoxs6.Text) 'Tracking # box
End Sub

Private Sub TextBoxs7_Change()
    TextBoxs7.Text = UCase(TextBoxs7.Text) 'QTY of boxes/pallet/crate box
End Sub

Private Sub TextBoxs8_Change()
    TextBoxs8.Text = UCase(TextBoxs8.Text) 'Weight in LBS box
End Sub

Private Sub TextBoxs9_Change()
    TextBoxs9.Text = UCase(TextBoxs9.Text) 'NCR # box
End Sub

Private Sub UserForm_Activate()

TextBoxs1.Text = Format(Now(), "MM/DD/YY") 'Date format to today without resetting manually


End Sub
Private Sub CommandButton2_Click()

erow = Sheets("Corp Shipping").Range("a" & Rows.Count).End(xlUp).Row
    Range("A" & erow + 1) = TextBoxs1.Value 'Date
    Range("B" & erow + 1) = TextBoxs2.Value 'Customer
    Range("C" & erow + 1) = TextBoxs3.Value 'Project
    Range("D" & erow + 1) = TextBoxs4.Value 'Items #'s or Packing slip #
    Range("E" & erow + 1) = TextBoxs5.Value 'Carrier
    Range("F" & erow + 1) = TextBoxs6.Value 'Tracking #
    Range("G" & erow + 1) = TextBoxs7.Value '# of boxes
    Range("H" & erow + 1) = TextBoxs8.Value & "LBS" 'Weight
    Range("I" & erow + 1) = "NCR " & TextBoxs9.Value 'NCR # Customer and Ours
    Range("J" & erow + 1) = "RMA " & TextBoxs10.Value 'Our RMA #
    Range("K" & erow + 1) = TextBoxs11.Value 'Customer RMA #
   
    TextBoxs2.Value = "" 'Removes after submit
    TextBoxs3.Value = "" 'Removes after submit
    TextBoxs4.Value = "" 'Removes after submit
    TextBoxs5.Value = "" 'Removes after submit
    TextBoxs6.Value = "" 'Removes after submit
    TextBoxs7.Value = "" 'Removes after submit
    TextBoxs8.Value = "" 'Removes after submit
    TextBoxs9.Value = "" 'Removes after submit
    TextBoxs10.Value = "" 'Removes after submit
    TextBoxs11.Value = "" 'Removes after submit

End Sub

 

[Logit]

Check your spelling. You are using TextBox1, TextBox2, etc. in some of the code lines .... and then you are using TextBoxs1, TextBoxs2, etc. in other areas.

There are no TextBoxs in Excel that I am aware of.

 

[Jon Peltier]

Further to Logit's post, you should get into the habit of giving UserForm controls names that are meaningful. TextBox1 only tells me it's a text box; txtFirstName gives me a hint about what the textbox is for..

 

[PuntingJawa]

Check your spelling. You are using TextBox1, TextBox2, etc. in some of the code lines .... and then you are using TextBoxs1, TextBoxs2, etc. in other areas.

There are no TextBoxs in Excel that I am aware of.

I changed the name of the boxes to test if it wasn't working because the names "textbox1, textbox2, ect" being the same between Receiving and Shipping was causing the issue. This isn't the case it seems.

Further to Logit's post, you should get into the habit of giving UserForm controls names that are meaningful. TextBox1 only tells me it's a text box; txtFirstName gives me a hint about what the textbox is for..

I used comments on the right side of the Shipping log. I was coming up on the end of the day and hadn't had time to comment on the Receiving ones. I'll repost below

Layout of UserForm and all boxes used.

Receiving (Working with suggested updates of Unique names. (Starts with Rec and a description "RecDate" ect)

Private Sub RecDate_Change() 'Date

End Sub

Private Sub RecNCR_Change()
    RecNCR.Text = UCase(RecNCR.Text) 'NCR #
End Sub

Private Sub RecRMA_Change()
    RecRMA.Text = UCase(RecRMA.Text) 'RMA #
End Sub

Private Sub RecSupplier_Change()
    RecSupplier.Text = UCase(RecSupplier.Text) 'Supplier
End Sub

Private Sub RecProject_Change()
    RecProject.Text = UCase(RecProject.Text) 'Project #
End Sub

Private Sub RecDescription_Change()
    RecDescription.Text = UCase(RecDescription.Text) 'Item Description
End Sub

Private Sub RecPartNumber_Change()
    RecPartNumber.Text = UCase(RecPartNumber.Text) 'Part #
End Sub

Private Sub RecSlipQTY_Change()
    RecSlipQTY.Text = UCase(RecSlipQTY.Text) 'QTY on packing slip
End Sub

Private Sub RecCountQTY_Change()
    RecCountQTY.Text = UCase(RecCountQTY.Text) 'Actual QTY count
End Sub

Private Sub RecPackNumber_Change()
    RecPackNumber.Text = UCase(RecPackNumber.Text) 'Packing Slip #
End Sub

Private Sub RecPO_Change()
    RecPO.Text = UCase(RecPO.Text) 'PO #
End Sub

Private Sub UserForm_Activate()

RecDate.Text = Format(Now(), "MM/DD/YY") 'Auto date for today


End Sub
Private Sub CommandButton1_Click() 'Command button submit

erow = Sheets("Receiving").Range("a" & Rows.Count).End(xlUp).Row 'Sumbit on next open "A" row
    Range("A" & erow + 1) = RecDate.Value 'Date
    Range("B" & erow + 1) = RecSupplier.Value 'Supplier
    Range("C" & erow + 1) = RecProject.Value 'Project #
    Range("D" & erow + 1) = RecDescription.Value 'Decription
    Range("E" & erow + 1) = RecPartNumber.Value 'Part #
    Range("F" & erow + 1) = RecSlipQTY.Value 'Packing slip QTY
    Range("G" & erow + 1) = RecCountQTY.Value 'Actual count QTY
    Range("I" & erow + 1) = "PS " & RecPackNumber.Value 'Pack Slip #
    Range("J" & erow + 1) = "PO " & RecPO.Value 'Purchase Order #
    Range("K" & erow + 1) = RecNCR.Value 'NCR #
    Range("L" & erow + 1) = RecRMA.Value 'RMA #

End Sub

Shipping log (Not Working) Same format.

Private Sub ShipDate_Change() 'Date box

End Sub

Private Sub ShipRMA_Change()
    ShipRMA.Text = UCase(ShipRMA.Text) 'Our RMA box
End Sub

Private Sub ShipCustomerRMA_Change()
    ShipCustomerRMA.Text = UCase(ShipCustomerRMA.Text) 'Customer RMA box
End Sub

Private Sub ShipCustomer_Change()
    ShipCustomer.Text = UCase(ShipCustomer.Text) 'Customer Name box
End Sub

Private Sub ShipProject_Change()
    ShipProject.Text = UCase(ShipProject.Text) 'Project Code box
End Sub

Private Sub ShipItems_Change()
    ShipItems.Text = UCase(ShipItems.Text) 'Packing # or Item #'s and QTYs box
End Sub

Private Sub ShipCarrier_Change()
    ShipCarrier.Text = UCase(ShipCarrier.Text) 'Carrier company box
End Sub

Private Sub ShipTracking_Change()
    ShipTracking.Text = UCase(ShipTracking.Text) 'Tracking # box
End Sub

Private Sub ShipPackQTY_Change()
    ShipPackQTY.Text = UCase(ShipPackQTY.Text) 'QTY of boxes/pallet/crate box
End Sub

Private Sub ShipWeight_Change()
    ShipWeight.Text = UCase(ShipWeight.Text) 'Weight in LBS box
End Sub

Private Sub ShipNCR_Change()
    ShipNCR.Text = UCase(ShipNCR.Text) 'NCR # box
End Sub

Private Sub UserForm_Activate()

ShipDate.Text = Format(Now(), "MM/DD/YY") 'Date format to today without resetting manually


End Sub
Private Sub CommandButton2_Click()

erow = Sheets("Shipping").Range("a" & Rows.Count).End(xlUp).Row 'Sumbmit on next open "A" row
    Range("A" & erow + 1) = ShipDate.Value 'Date
    Range("B" & erow + 1) = ShipCustomer.Value 'Customer
    Range("C" & erow + 1) = ShipProject.Value 'Project
    Range("D" & erow + 1) = ShipItems.Value 'Items #'s or Packing slip #
    Range("E" & erow + 1) = ShipCarrier.Value 'Carrier
    Range("F" & erow + 1) = ShipTracking.Value 'Tracking #
    Range("G" & erow + 1) = ShipPackQTY.Value '# of boxes
    Range("H" & erow + 1) = ShipWeight.Value & "LBS" 'Weight
    Range("I" & erow + 1) = "NCR " & ShipNCR.Value 'NCR # Customer and Ours
    Range("J" & erow + 1) = "RMA " & ShipRMA.Value 'Our RMA #
    Range("K" & erow + 1) = ShipCustomerRMA.Value 'Customer RMA #
    
    ShipProject.Value = "" 'Removes after submit
    ShipCustomer.Value = "" 'Removes after submit
    ShipItems.Value = "" 'Removes after submit
    ShipCarrier.Value = "" 'Removes after submit
    ShipTracking.Value = "" 'Removes after submit
    ShipPackQTY.Value = "" 'Removes after submit
    ShipWeight.Value = "" 'Removes after submit
    ShipNCR.Value = "" 'Removes after submit
    ShipRMA.Value = "" 'Removes after submit
    ShipCustomerRMA.Value = "" 'Removes after submit

End Sub

 

[PuntingJawa]

With the free time I've had I've tried several things. I have found that the UserForms only work on Sheet3(receiving). I've tried several different formula like replacing the current

erow = Sheets("Shipping").Range("a" & Rows.Count).End(xlUp).Row

command with

erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

to try and force it to be used on the current active but I come back with a debug error. I still can't figure out why it won't work on Sheet1(shipping) for the life of me. I did manage to switch the clear box function to a button instead though (Tested and works) so I'm making a little progress in other areas.

 

[dmt32]

Hi,
untested but see if this update to your commandbutton code helps

Private Sub CommandButton2_Click()
    Dim erow As Long
    With ThisWorkbook.Worksheets("Shipping")
     'Sumbmit on next open "A" row
        erow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        .Cells(erow, 1) = ShipDate.Value 'Date
        .Cells(erow, 2) = ShipCustomer.Value 'Customer
        .Cells(erow, 3) = ShipProject.Value 'Project
        .Cells(erow, 4) = ShipItems.Value 'Items #'s or Packing slip #
        .Cells(erow, 5) = ShipCarrier.Value 'Carrier
        .Cells(erow, 6) = ShipTracking.Value 'Tracking #
        .Cells(erow, 7) = ShipPackQTY.Value '# of boxes
        .Cells(erow, 8) = ShipWeight.Value & "LBS" 'Weight
        .Cells(erow, 9) = "NCR " & ShipNCR.Value 'NCR # Customer and Ours
        .Cells(erow, 10) = "RMA " & ShipRMA.Value 'Our RMA #
        .Cells(erow, 11) = ShipCustomerRMA.Value 'Customer RMA #
    End With
    
    ShipProject.Value = "" 'Removes after submit
    ShipCustomer.Value = "" 'Removes after submit
    ShipItems.Value = "" 'Removes after submit
    ShipCarrier.Value = "" 'Removes after submit
    ShipTracking.Value = "" 'Removes after submit
    ShipPackQTY.Value = "" 'Removes after submit
    ShipWeight.Value = "" 'Removes after submit
    ShipNCR.Value = "" 'Removes after submit
    ShipRMA.Value = "" 'Removes after submit
    ShipCustomerRMA.Value = "" 'Removes after submit

End Sub

Dave

 

[PuntingJawa]

Sorry, Dave. The "shipping" userform is doing the same as before except this time it doesn't work the "receiving" log like the other did. I know I am missing something minor. I was hoping to salvage what I had done but may need to start over and simply redo the entire thing. This is just a quality of life for a manual log of my personal use as a backup.

 

[Jon Peltier]

I don't see where you defined what "works" and "doesn't work" mean for these UserForms. The code looks like it "should work".

Does not working mean one of these?

• UserForm doesn't appear
• Entered items are not capitalized
• Next open row isn't calculated properly
• Next open row isn't populated correctly

 

[PuntingJawa]

Ah! Sorry about that. I thought I already had. I'll clarify.
Data is not being populated in the worksheet after clicking on the submit command button on the "Shipping" userform. The userform itself is perfect. All boxes allow for typing, auto date enters as intended, and caps is forced as intended.

What I am having issues understanding is why "Receiving" userform is working as intended but the "Shipping" userform is not since they are nearly identical.

 

[mikerickson]

When there are problems with writting to a sheet, fully qualifying the reference (adding Workbooks("Sheet1") to the code) is my first goto fix.