VBA and UserForm Help

L

lilbuggs

New Member
Joined
Aug 25, 2014
Messages
33
I am working on a project that is as follows below. I am having a hard time with the CommandButton portions of the below. I know what I want to do in English but can't seem to type it out into VBA. I am storing the data of the table into multiple arrays.
UserForm setup is as follows and looks like the attached
photo.php
:
• An employee will select one of the 12 items from a ComboBox and type the number of units requested in a TextBox.
• Next, the employee will click a CommandButton and the program will attempt to subtract the number of units requested from the existing inventory.
◦ If the number of units requested is less than or equal to the number of units still in stock in column C, the value in column C will be updated and this updated value will be displayed on the UserForm in a TextBox. If the updated value is less than 5, a message box will tell the employee that the store is running out of that item. The message box should include the catalog number and number of items in stock.
◦ However, if the number of units requested is greater than the number of units still in stock in column C, then a message box will tell the employee that the order cannot be met. The value in column C will not be updated.
• There will be a second CommandButton that only determines the number of units in stock for the item selected in the ComboBox. This CommandButton will not update column C.
• There will be a third CommandButton that quits the UserForm.

Table looks like the following in Columns A-C and Rows 1-16:
Item NameItem Catalog NumberUnits In Stock
Paper - wide rule - 100 sheets1001017
Paper - wide rule - 200 sheets1002023
Paper - wide rule - 500 sheets1003010
Pencils - #2 - 10 pack1011019
Pencils - #2 - 20 pack101209
Highlighters - yellow - 1 pack1021020
Highlighters - yellow - 3 pack1022034
Highlighters - pink - 1 pack1023021
Highlighters - pink - 3 pack1024050
Paper clips - small - 100 count1031028
Paper clips - small - 200 count103207
Paper clips - large - 100 count1033012

<tbody>
</tbody>

Current code is:
Option Explicit

Private Sub Combo_Item_Change()

End Sub

Private Sub Combo_Item_DropButt*******()
Combo_Item.List = Array("Paper - wide rule - 100 sheets", "Paper - wide rule - 200 sheets", "Paper - wide rule - 500 sheets", "Pencils - #2 - 10 pack", "Pencils - #2 - 20 pack", "Highlighters - yellow - 1 pack", "Highlighters - yellow - 3 pack", "Highlighters - pink - 1 pack", "Highlighters - pink - 3 pack", "Paper clips - small - 100 count", "Paper clips - small - 200 count", "Paper clips - large - 100 count")
End Sub

Private Sub Command_ChkStock_Click()

End Sub

Private Sub Command_Quit_Click()
Unload UserForm1
End Sub

Private Sub Command_Run_Click()
Dim rNum As Integer, cNum As Integer
Dim inventory() As String
Dim item() As String
Dim catNum() As String
Dim itemcnt() As String
Dim i As Integer, j As Integer
Dim UnitsReq As String, ItemName As String

rNum = Worksheets("Sheet2").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
cNum = Cells(1, Columns.Count).End(xlToLeft).Column
UnitsReq = Text_UnitsReq.Value 'value of units requested placed in text box
ItemName = Combo_Item.Value 'value of item chosen from combo box

ReDim inventory(rNum, cNum) As String 'pull all detail into an array
ReDim item(rNum, 0) As String 'pull column A into an array of items
ReDim catNum(rNum, 1) As String 'pull column B into an array of catalog numbers
ReDim itemcnt(rNum, 2) As String 'pull column C into an array of items in stock
Range("A2").Select
For i = 1 To rNum
For j = 1 To cNum
inventory(i, j) = ActiveCell.Offset(i - 1, j - 1).Value
item(i, 0) = ActiveCell.Offset(i - 1, 0).Value
catNum(i, 1) = ActiveCell.Offset(i - 1, 1).Value
itemcnt(i, 2) = ActiveCell.Offset(i - 1, 2).Value
Next j
Next i

End Sub

Anyone want to try this brain teaser out? Any help is appreciated!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
Hi,
Not sure that I have fully followed your requirement but think you may over complicating what you are trying to achieve.
See if following helps:

Your form will need the following controls:

4 textboxes named:
Text_Catalog
Text_StockBal
Text_ItemsInStock
Text_UnitsReq

2 CommandButtons Named:
Command_Run
Command_Quit

A ComboBox Named:
Combo_Item

Code: 
Dim ws As Worksheet
Dim RowNo As Long

Private Sub Command_Run_Click()
ws.Cells(RowNo, 3).Value = Val(Me.Text_StockBal.Text)
Call Combo_Item_Change
End Sub


Private Sub Command_Quit_Click()
Unload Me
End Sub


Private Sub Combo_Item_Change()
    
    RowNo = Me.Combo_Item.ListIndex + 2
    
    Me.Text_Catalog.Text = ws.Cells(RowNo, 2).Value
    Me.Text_ItemsInStock.Text = ws.Cells(RowNo, 3).Value
    Me.Text_UnitsReq.Text = 0
    Call Text_UnitsReq_Change
End Sub


Private Sub Text_UnitsReq_Change()
    Me.Text_StockBal.Text = Val(Me.Text_ItemsInStock) - Val(Me.Text_UnitsReq)
    Me.Command_Run.Enabled = Not Val(Me.Text_StockBal.Text) < 0
End Sub


Private Sub UserForm_Initialize()
    Set ws = Worksheets("Sheet2")
    
    Me.Text_Catalog.Locked = True
    Me.Text_StockBal.Locked = True
    Me.Text_ItemsInStock.Locked = True
    
    Me.Combo_Item.List = Array("Paper - wide rule - 100 sheets", "Paper - wide rule - 200 sheets", _
                            "Paper - wide rule - 500 sheets", "Pencils - #2 - 10 pack", _
                            "Pencils - #2 - 20 pack", "Highlighters - yellow - 1 pack", _
                            "Highlighters - yellow - 3 pack", "Highlighters - pink - 1 pack", _
                            "Highlighters - pink - 3 pack", "Paper clips - small - 100 count", _
                            "Paper clips - small - 200 count", "Paper clips - large - 100 count")


End Sub

Place all code in your forms code page. Note the variables sitting outside of any procedure which must be placed at the TOP of the code page.
Code uses the comboboxes listindex property to navigate to the correct row on your sheet2.
You may want to consider placing the list for your combobox in a separate sheet & reference that rather than hard coding – this will make list easier to update.
Solution may not be exactly what you are after but hopefully, will give you some ideas.

Hope Helpful

Dave
 
Upvote 0
This is an ungraded assignment for a free VBA MOOC class that I've been taking. In looking at your solution with the requirements, there should be two text boxes total, three command buttons, and a combo box. Everything has to be stored on the one sheet with in the work book, and a macro is created to run the UserForm. I'm having the hard time telling the program that when a selection is made in the combo box to look at column C for the units in stock, subtract what the requested units are, and output the new unit amount with the required message boxes. The requirements for the actual assignment are as follows:

Problem 2

Concept – UserForms, If structures
Difficulty level – Medium-Difficult

Complete this problem in Sheet2.

You work at a small store that sells 12 items. The current inventory of all items is saved on Sheet2 of a worksheet – the item name is listed in column A, the item catalog number is listed in column B, and the number of units left in stock is listed in column C. For example, a three-pack of yellow highlighters has part number 10220 and there are 34 units in stock.

Item Name Item Catalog Number Units In Stock
Paper - wide rule - 100 sheets 10010 17
Paper - wide rule - 200 sheets 10020 23
Paper - wide rule - 500 sheets 10030 10

Pencils - #2 - 10 pack 10110 19
Pencils - #2 - 20 pack 10120 9

Highlighters - yellow - 1 pack 10210 20
Highlighters - yellow - 3 pack 10220 34
Highlighters - pink - 1 pack 10230 21
Highlighters - pink - 3 pack 10240 50

Paper clips - small - 100 count 10310 28
Paper clips - small - 200 count 10320 7
Paper clips - large - 100 count 10330 12

Your boss knows that you have been taking a course in Excel VBA programming and asks you to make a UserForm that would allow the company to keep track of inventory easily and efficiently. Here are the specifications for the UserForm:

When a purchase request is made by a customer…
• An employee will select one of the 12 items from a ComboBox and type the number of units requested in a TextBox.
• Next, the employee will click a CommandButton and the program will attempt to subtract the number of units requested from the existing inventory.
◦ If the number of units requested is less than or equal to the number of units still in stock in column C, the value in column C will be updated and this updated value will be displayed on the UserForm in a TextBox. If the updated value is less than 5, a message box will tell the employee that the store is running out of that item. The message box should include the catalog number and number of items in stock.
◦ However, if the number of units requested is greater than the number of units still in stock in column C, then a message box will tell the employee that the order cannot be met. The value in column C will not be updated.
• There will be a second CommandButton that only determines the number of units in stock for the item selected in the ComboBox. This CommandButton will not update column C.

Use Option Explicit for this UserForm.

For example:
If the user selects Highlighters - yellow - 3 pack from the ComboBox and clicks the second CommandButton, a message box will appear that states, "There are 34 Highlighters - yellow - 3 pack left in inventory."

Next, if the user selects Highlighters - yellow - 3 pack from the ComboBox and types 45 in the TextBox, then clicks the first CommandButton, a message box will appear that states "There are only 34 Highlighters - yellow - 3 pack left in inventory. The order cannot be met." The number of 34 will still appear in cell C10.

Next, if the user selects Highlighters - yellow - 3 pack from the ComboBox and types 5 in the TextBox, then clicks the first CommandButton, the number 29 will appear in cell C10 and in the TextBox that displays the current number of units in stock.

Finally, if the user selects Highlighters - yellow - 3 pack from the ComboBox and types 25 in the TextBox, then clicks the first CommandButton, the number 4 will appear in cell C10 and in the TextBox that displays the current number of units in stock. Additionally, a message box will state "There are only 4 units of Highlighters - yellow - 3 pack left in inventory. Order part number 10220 soon!"

Use Option Explicit for this macro.

If this problem is too easy for you, here is one way to make it more difficult:
(1) Create a CommandButton on the UserForm that will undo the last action taken by the employee.
 
Upvote 0
I'm having the hard time telling the program that when a selection is made in the combo box to look at column C for the units in stock, subtract what the requested units are
Click to expand...

My solution shows you how to do this by using comboboxes listindex property to determine to records Row number.

Suggest set-up a temp form to match my suggestion & have a play to understand how it works & you will see how the calculation bit is working. It may not match this requirement but should give you some pointers.

Very sorry, but cannot work through the whole assignment for you.

Dave
 
Upvote 0
I appreciate your help. Not expecting anyone to work through the assignment. I've actually been playing with what you sent me and trying to set things up the way you have to see how it all works. Just wanted to provide more background as to what I was trying to accomplish and why I was doing it the way I was. Thanks.

dmt32 said:
My solution shows you how to do this by using comboboxes listindex property to determine to records Row number.

Suggest set-up a temp form to match my suggestion & have a play to understand how it works & you will see how the calculation bit is working. It may not match this requirement but should give you some pointers.

Very sorry, but cannot work through the whole assignment for you.

Dave
Click to expand...
 
Upvote 0
lilbuggs said:
I appreciate your help. Not expecting anyone to work through the assignment. I've actually been playing with what you sent me and trying to set things up the way you have to see how it all works. Just wanted to provide more background as to what I was trying to accomplish and why I was doing it the way I was. Thanks.
Click to expand...

No worry as said, did not fully understand from your first post why you were taking such an approach.

hope you manage to work through it - as a friendly tip - in the UserForm_Initialize I posted try replacing this:

Code: 
 Me.Combo_Item.List = Array("Paper - wide rule - 100 sheets", "Paper - wide rule - 200 sheets", _
                            "Paper - wide rule - 500 sheets", "Pencils - #2 - 10 pack", _
                            "Pencils - #2 - 20 pack", "Highlighters - yellow - 1 pack", _
                            "Highlighters - yellow - 3 pack", "Highlighters - pink - 1 pack", _
                            "Highlighters - pink - 3 pack", "Paper clips - small - 100 count", _
                            "Paper clips - small - 200 count", "Paper clips - large - 100 count")

with this:

Code: 
    With Sheets("Sheet2")
         Me.Combo_Item.List = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value
    End With

This reads the list dynamically (expands if you add to list) directly from the worksheet & negates need to hard code.

Good Luck

Dave
 
Upvote 0
Thanks for your help! I made the change below and edited the code a bit to match the assignment and it's working perfectly. Just in case you care, here is what I came up with :) Again I appreciate the help!

Option Explicit
Dim ws As Worksheet
Dim RowNo As Long

Private Sub Command_Run_Click()
Text_StockBal.Text = Val(Text_ItemsInStock) - Val(Text_UnitsReq)
Command_Run.Enabled = Not Val(Text_StockBal.Text) < 0

If Val(Text_UnitsReq) > Val(Text_ItemsInStock) Then
MsgBox "There are " & ws.Cells(RowNo, 3).Value & " " & ws.Cells(RowNo, 1).Value & " left in inventory. The order cannot be met."
ElseIf Val(Text_StockBal.Text) < 5 Then
MsgBox "There are only " & Text_StockBal.Text & " units of " & ws.Cells(RowNo, 1).Value & " left in inventory. Order part number " & Text_Catalog.Text & " soon!"
ws.Cells(RowNo, 3).Value = Val(Text_StockBal.Text)
Else
ws.Cells(RowNo, 3).Value = Val(Text_StockBal.Text)
End If

End Sub

Private Sub Command_Quit_Click()
Unload UserForm2
End Sub

Private Sub Combo_Item_Change()

End Sub

Private Sub Command_StockBal_Click()
RowNo = Combo_Item.ListIndex + 2

Text_Catalog.Text = ws.Cells(RowNo, 2).Value
Text_ItemsInStock.Text = ws.Cells(RowNo, 3).Value
Text_UnitsReq.Text = 0
Text_StockBal.Text = ""

MsgBox "There are " & ws.Cells(RowNo, 3).Value & " " & ws.Cells(RowNo, 1).Value & " left in inventory."

End Sub


Private Sub UserForm_Initialize()
Set ws = Worksheets("Sheet2")

Text_Catalog.Locked = True
Text_StockBal.Locked = True
Text_ItemsInStock.Locked = True

With Sheets("Sheet2")
Combo_Item.List = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value
End With

End Sub

dmt32 said:
No worry as said, did not fully understand from your first post why you were taking such an approach.

hope you manage to work through it - as a friendly tip - in the UserForm_Initialize I posted try replacing this:

Code: 
 Me.Combo_Item.List = Array("Paper - wide rule - 100 sheets", "Paper - wide rule - 200 sheets", _
                            "Paper - wide rule - 500 sheets", "Pencils - #2 - 10 pack", _
                            "Pencils - #2 - 20 pack", "Highlighters - yellow - 1 pack", _
                            "Highlighters - yellow - 3 pack", "Highlighters - pink - 1 pack", _
                            "Highlighters - pink - 3 pack", "Paper clips - small - 100 count", _
                            "Paper clips - small - 200 count", "Paper clips - large - 100 count")

with this:

Code: 
    With Sheets("Sheet2")
         Me.Combo_Item.List = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value
    End With

This reads the list dynamically (expands if you add to list) directly from the worksheet & negates need to hard code.

Good Luck

Dave
Click to expand...
 
Upvote 0
Hi,
thanks for feedback - wish you well with assignment.
Just comment - may want to review this line:

Code: 
Command_Run.Enabled = Not Val(Text_StockBal.Text) < 0

You have placed it in Command_Run code itself & if it evaluates False you do not have anything in your project to Enable it.

Good luck

Dave
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
VBA Inventory UserForm ID generation
Replies
1
Views
296
Herakles
H
Oberon70
  • Solved
Help with For Loop - works great except for the last bit
2 3
Replies
23
Views
890
Oberon70
Oberon70
H
  • Solved
VBA to extract most Used Folders
Replies
4
Views
277
howard
H
F
  • Solved
Filling out Text in Excel Cell Based on Highlighted Text in Microsoft Word
Replies
5
Views
435
NdNoviceHlp
NdNoviceHlp
B
  • Solved
Object variable or with block variable not set
Replies
2
Views
270
Bagsy
B

Forum statistics

Threads
1,203,214
Messages
6,054,202
Members
444,708
Latest member
David R__

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