Multiple Userform One works the other not

PuntingJawa

PuntingJawa

Board Regular
Joined
Feb 25, 2021
Messages
162
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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)
VBA Code: 
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)
VBA Code: 
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
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.
 
Upvote 0
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..
 
Upvote 0
Logit said:
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.
Click to expand...
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.

Jon Peltier said:
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..
Click to expand...
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.
1651841045990.png


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

VBA Code: 
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.

VBA Code: 
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
 
Upvote 0
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
VBA Code: 
erow = Sheets("Shipping").Range("a" & Rows.Count).End(xlUp).Row
command with
VBA Code: 
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.
 
Upvote 0
Hi,
untested but see if this update to your commandbutton code helps

VBA Code: 
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
When there are problems with writting to a sheet, fully qualifying the reference (adding Workbooks("Sheet1") to the code) is my first goto fix.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
VBA Generate email with multiple data in body.
Replies
2
Views
127
Logit
Logit
I
  • Solved
Listbox on userform to show results in reverse order
2
Replies
16
Views
350
Fuji
F
C
  • Question Question
Linking two sheets in different workbooks takes a long time even with only 500 rows of data. Is there a quicker, more efficient way to do this?
Replies
0
Views
178
chazrab
C
K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
236
krunal123
K
J
  • Question Question
Excel VBA Print Name Tag from Userform
Replies
2
Views
372
Rhodie72
Rhodie72

Forum statistics

Threads
1,224,814
Messages
6,181,125
Members
453,021
Latest member
Justyna P

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