Complicated User Form

[mayrose]

I am familiar with Excel, but am very new to the VBA Language, but am creating a UserForm that has complicated commands for Macros to carry out. The following is what I need my workbook to do. I wrote it in plain text and hope that it makes sense. Thank you for any help or direction you can give me!!!

Activate Inventory Sheet
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Selection Made From Combo Box on user form:
<o> </o>
If Cycle Item1 is selected, Data goes into row 5 (Constant)
If Cycle Item2 is selected, Data goes into row6 (Constant)
If Cycle Item3 is selected, Data goes into row7 (Constant)
<o> </o>
Value Input into Textbox on User Form:
<o> </o>
BegInv Textbox value goes into column D
Receipts Textbox Value goes into column E
Adjust In Textbox Value goes into column F
Adjust Out Textbox Value goes into column G
<o> </o>
Selection Made from Combo Box on User Form
<o> </o>
If Monday, Regular Hours selected, Product Volume Textbox Value goes into ColumnH: Number of Hours Textbox Value goes into ColumnI
If Monday, Overtime Hours selected, Product Volume Textbox Value goes into ColumnJ: Number of Hours Textbox Value goes into ColumnK
If Monday, Part Time Extra Hours selected, Product Volume Textbox Value goes into ColumnL: Number of Hours Textbox Value goes into ColumnM
If Tuesday, Regular Hours selected, Product Volume Textbox Value goes into ColumnN: Number of Hours Textbox Value goes into ColumnO
If Tuesday, Overtime Hours selected, Product Volume Textbox Value goes into ColumnP: Number of Hours Textbox Value goes into ColumnQ
If Tuesday, Part Time Extra Hours selected, Product Volume Textbox Value goes into ColumnR: Number of Hours Textbox Value goes into ColumnS
If Wednesday, Regular Hours selected, Product Volume Textbox goes into ColumnT: Number of Hours Textbox Value goes into ColumnU
If Wednesday, Overtime Hours selected, Product Volume Textbox goes into ColumnV: Number of Hours Textbox Value goes into ColumnW
If Wednesday, Part Time Extra Hours selected, Product Volume Textbox Value goes into ColumnX: Number of Hours Textbox Value goes into ColumnY
If Thursday, Regular Hours selected, Product Volume Textbox Value goes into ColumnZ: Number of Hours Textbox Value goes into ColumnAA
If Thursday, Overtime Hours selected, Product Volume Textbox Value goes into ColumnAB: Number of Hours Textbox Value goes into ColumnAC
If Thursday, Part Time Extra Hours selected, Product Volume Textbox Value goes into ColumnAD: Number of Hours Textbox Value goes into ColumnAE
If Friday, Regular Hours selected, Product Volume Textbox Value goes into Column AF: Number of Hours Textbox Value goes into Column AG
If Friday, Overtime Hours selected, Product Volume Textbox Value goes into Column AH: Number of Hours Textbox Value goes into Column AI
If Friday, Part Time Hours selected, Product Volume Textbox Value goes into Column AJ: Number of Hours Textbox Value goes into <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace w:st="on"><st1:City w:st="on">Column</st1:City> <st1:State w:st="on">AK</st1:State></st1lace>
If Saturday, Overtime Hours selected, Product Volume Textbox Value goes into Column AL: Number of Hours Textbox value goes into Column AM
If Saturday, Part Time Extra Hours selected, Product Volume Textbox Value goes into ColumnAN: Number of Hours Textbox Value goes into Column AO.
<o> </o>
<o> </o>
<o> </o>

 

[RolfJ]

Give this code a try. It makes the following assumptions:

• The name of your inventory worksheet is "Inventory" (you may change that by assigning a different string value to the constant INVENTORYSHEET
• You copy the code into the VBA modules associated with the UserForm you created
• This UserForm has two ComboBoxes, named 'ComboBox_Cycle' and 'ComboBox_Hours'
• The UserForm has the following TextBoxes:
  • TextBox_BegInv
  • TextBox_Receipts
  • TextBox_AdjustIn
  • TextBox_AdjustOut
  • TextBox_ProductVolume
  • TextBox_ProductVolume
• The UserForm has a CommandButton named 'cmdBtn_OK'

Const INVENTORYSHEET = "Inventory"
Private Sub cmdBtn_OK_Click()
 
    Dim targetRow As Long
    targetRow = ComboBox_Cycle.ListIndex + 5
 
    Sheets(INVENTORYSHEET).Cells(targetRow, "D").Value = TextBox_BegInv.Text
    Sheets(INVENTORYSHEET).Cells(targetRow, "E").Value = TextBox_Receipts.Text
    Sheets(INVENTORYSHEET).Cells(targetRow, "F").Value = TextBox_AdjustIn.Text
    Sheets(INVENTORYSHEET).Cells(targetRow, "G").Value = TextBox_AdjustOut.Text
    Dim targetColumn As Integer
    targetColumn = 2 * ComboBox_Hours.ListIndex + 8
 
    Sheets(INVENTORYSHEET).Cells(targetRow, targetColumn).Value = TextBox_ProductVolume.Text
    Sheets(INVENTORYSHEET).Cells(targetRow, targetColumn + 1).Value = TextBox_ProductVolume.Text
 
End Sub
Private Sub UserForm_Activate()
 
    Sheets(INVENTORYSHEET).Activate
 
    'Populate ComboBoxes
 
    ComboBox_Cycle.Clear
    ComboBox_Cycle.AddItem ("Cycle Item 1")
    ComboBox_Cycle.AddItem ("Cycle Item 2")
    ComboBox_Cycle.AddItem ("Cycle Item 3")
    ComboBox_Cycle.ListIndex = 0
 
    ComboBox_Hours.Clear
    Dim iDay As Integer
    For iDay = 1 To 6
        Dim strWeekDayName As String
        strWeekDayName = weekDayName(iDay, False, vbMonday)
        If strWeekDayName <> "Saturday" Then ComboBox_Hours.AddItem (strWeekDayName & ", Regular Hours")
        ComboBox_Hours.AddItem (strWeekDayName & ", Overtime Hours")
        ComboBox_Hours.AddItem (strWeekDayName & ", Part Time Extra Hours")
    Next iDay
    ComboBox_Hours.ListIndex = 0
 
End Sub

Hope this helped,
Rolf Jaeger
SoarentComputing
http://soarentcomputing.com

Phone: 800.580.0068
Cell: 510.300.7462