Using a UserForm to dynamically accept values that will go in certain cells.

DrParmeJohnson

New Member
Joined
Feb 28, 2019
Messages
44
Hello,

I'm trying to find a way to prompt a user to input some data that could be one thing or could be many. I've looked a bit and it seems that perhaps using a UserForm is the best way to accomplish this but, I do not know how to do that.

All I have so far is as follows.
VBA Code:
    Dim Addon As Integer
 
    Addon = MsgBox("Are there Add-Ons?", vbQuestion + vbYesNo + vbDefaultButton2, "Add-Ons")
    
    If Addon = vbYes Then
    'Use the UserForm to collect the necessary data.

    End If

The data the form would take would be in 3 values.
They will go into the columns as follows.

Value1 will be an id, value2 will be a description and value3 will be a qty.

idDescriptionQty
pre-existingpre-existingpre-existing
pre-existingpre-existingpre-existing
value1value2value3
value4value5value6

Values 1,2 & 3 will be a single product and so they would have to go on the same line.

If the info can be supplied as something like "Value1,Value2,Value3" and then placed into their respective columns, that would be all that is needed.

But, sometimes there might be numerous products and I'm not sure if it is possible to take a dynamic number of inputs into the form.
I don't know if there is a way to ask "How many Add-ons do you have" [Input Number] and then generate a userform with that many input boxes or something but again, I don't know.

I have absolutely zero idea how userforms work or if it is even the best way to accomplish this but, some guidance would be appreciated nonetheless.
Please ask if there are any questions or things I left out.

Thank you.
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

DrParmeJohnson

New Member
Joined
Feb 28, 2019
Messages
44
Made a bit of progress on the Userform but, everytime I try to run my code, it will get to the Userform.Show portion and then give me an object not defined error and bug out. I don't really know what I've messed up. Here's the form code and the macro.

VBA Code:
    'Not a clue on which variables to use here
    Dim formAddOns As Object
    Dim Addon As Integer
    Addon = MsgBox("Are there Add-Ons?", vbQuestion + vbYesNo + vbDefaultButton2, "Add-Ons")
    
    If Addon = vbYes Then
    formAddOns.Show vbModeless

    End If

VBA Code:
Private Sub addInsertAddOn_Click()
Dim emptyrow As Long

'Focus on main Sheet
ws1.Activate

'Find Last Empty Row
emptyrow = Lastrow + 1

'Populate Data
Cells(emptyrow, 1).Value = addIDBox.Value
Cells(emptyrow, 2).Value = addDescBox.Value
Cells(emptyrow, 3).Value = addQtyBox.Value


End Sub

Private Sub addCancel_Click()
    Unload Me

End Sub

Private Sub UserForm_Initialize()

Load formAddOns

'Empty Box On Load
addIDBox.Value = ""
addDescBox.Value = ""
addQtyBox.Value = ""

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,861
Messages
5,627,315
Members
416,239
Latest member
Counselor85027

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
Top