Looking for guidance on how to finish up my VBA user form

DirectCommodity

New Member
Joined
Aug 25, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello-

I'm pretty novice at this stage in my old years, but I have a project for my small business that needs to be wrapped up. So far i've built out the "Deal Entry" user form that will input data into the "Deals" worksheet in my excel workbook. Now I know that Excel is not a "database", but for the short term, this will have to work until my full stack system is built.

Below is my form that I coded. Everything typed into the form will go to the designated worksheet under the right column... It has been tested and it does work "if manually entered " into the form.

1) What I would like help with is how to have each "combobox" provide a dropdown list of "pre-defined" items that are on several different worksheets and THEN input that completed form into the "Deals" worksheet.

2) Next i'd like to know what best way would be to have the "TradeID" textbox auto populate on any submitted form and remain locked from editing or duplicating based on a column of pre-defined numbers from a different worksheet. The reason for the locking is that even if the Trade is later edited or voided, i need to store a copy of the data for compliance/audit reasons.

3) For some reason my clear and close cmd buttons are not functional on click event- Only Send and X at the top corner will close the form

Deal_Form.png

VBA Code:
Private Sub CmdSave_Click()


     If Me.TextBoxTradeID.Value = "" Then
        MsgBox "Please enter a Trade ID", vbCritical
        
        Exit Sub
    End If
    
    
    If Me.ComboBoxProductName.Value = "" Then
        MsgBox "Please enter a Product", vbCritical
        
        Exit Sub
    End If
    
    If Me.ComboBoxLoc.Value = "" Then
    MsgBox "Please enter a valid Location", vbCritical
        
        Exit Sub
    End If
    
    If Me.ComboBoxPipe.Value = "" Then
    MsgBox "Please enter a valid Pipeline", vbCritical
    
        Exit Sub
    End If
    
    If Me.ComboBoxPricing.Value = "" Then
    MsgBox "Please enter a form of Pricing", vbCritical
    
        Exit Sub
    End If
    
    If Me.TextBoxVolume.Value = "" Then
    MsgBox "Please enter a Volume", vbCritical
    
        Exit Sub
    End If
    
    
    If Me.ComboBoxVolumeType.Value = "" Then
    MsgBox "Please enter either BBLS/DAY or BBLS", vbCritical
    
        Exit Sub
    End If
    
    If Me.TextBoxPrice.Value = "" Then
    MsgBox "Please enter a Price", vbCritical
    
        Exit Sub
    End If
    
    If Me.ComboBoxCurrency.Value = "" Then
    MsgBox "Please enter a Currency", vbCritical
    
        Exit Sub
    End If
    
    
    If Me.ComboBoxInvoice.Value = "" Then
    MsgBox "Please enter BBLS", vbCritical
    
        Exit Sub
    End If
    
    If Me.ComboBoxContract.Value = "" Then
    MsgBox "Please enter a either Buyer or Seller Company", vbCritical
    
        Exit Sub
    End If
    
    If Me.ComboBoxGTC.Value = "" Then
    MsgBox "Please enter a GT&C", vbCritical
    
        Exit Sub
    End If
    
          
    If Me.ComboBoxStartDate.Value = "" Then
    MsgBox "Please enter a Start Date", vbCritical
    
        Exit Sub
    End If
    
    If Me.ComboBoxEndDate.Value = "" Then
    MsgBox "Please enter a End Date", vbCritical
    
        Exit Sub
    End If
    
    
    If Me.ComboBoxBuyCompany.Value = "" Then
        MsgBox "Please enter a Buyer Company Name", vbCritical
        
        Exit Sub
    End If
    
    If Me.ComboBoxBuyTrader.Value = "" Then
    MsgBox "Please enter Buyer Trader Name", vbCritical
        
        Exit Sub
    End If
    
    
    If Me.TextBoxBuyTraderRate.Value = "" Then
    MsgBox "Please enter a Buyer Brokerage Rate", vbCritical
    
        Exit Sub
    End If
    
    If Me.ComboBoxBuyBroker.Value = "" Then
    MsgBox "Please enter a Buyer Broker Name", vbCritical
    
        Exit Sub
    End If
    
    If Me.ComboBoxSellCompany.Value = "" Then
        MsgBox "Please enter a Seller Company Name", vbCritical
        
        Exit Sub
    End If
    
    If Me.ComboBoxSellTrader.Value = "" Then
    MsgBox "Please enter Seller Trader Name", vbCritical
        
        Exit Sub
    End If
    
    
    If Me.TextBoxSellTraderRate.Value = "" Then
    MsgBox "Please enter a Seller Brokerage Rate", vbCritical
    
        Exit Sub
    End If
    
    If Me.ComboBoxSellBroker.Value = "" Then
    MsgBox "Please enter a Seller Broker Name", vbCritical
    
        Exit Sub
    End If
    
    
    If Me.TextBoxTimeStamp.Value = "" Then
    MsgBox "Please enter a Trade Date", vbCritical
    
        Exit Sub
    End If
                  
              
    
    
            
''''''' this code is to save the data into the sheets.


Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Deals")
Dim n As Long


n = sh.Range("A" & Application.Rows.Count).End(xlUp).row








sh.Range("A" & n + 1).Value = TextBoxTradeID.Value
sh.Range("B" & n + 1).Value = ComboBoxBuyCompany.Value
sh.Range("C" & n + 1).Value = ComboBoxBuyTrader.Value
sh.Range("D" & n + 1).Value = TextBoxBuyTraderRate.Value
sh.Range("E" & n + 1).Value = ComboBoxBuyBroker.Value
sh.Range("F" & n + 1).Value = ComboBoxProductName.Value
sh.Range("G" & n + 1).Value = ComboBoxLoc.Value
sh.Range("H" & n + 1).Value = ComboBoxPipe.Value
sh.Range("I" & n + 1).Value = ComboBoxPricing.Value
sh.Range("J" & n + 1).Value = TextBoxTerm.Value
sh.Range("K" & n + 1).Value = ComboBoxStartDate.Value
sh.Range("L" & n + 1).Value = ComboBoxEndDate.Value
sh.Range("M" & n + 1).Value = TextBoxVolume.Value
sh.Range("N" & n + 1).Value = ComboBoxVolumeType.Value
sh.Range("O" & n + 1).Value = TextBoxPrice.Value
sh.Range("P" & n + 1).Value = ComboBoxCurrency.Value
sh.Range("Q" & n + 1).Value = ComboBoxInvoice.Value
sh.Range("R" & n + 1).Value = ComboBoxContract.Value
sh.Range("S" & n + 1).Value = ComboBoxGTC.Value
sh.Range("T" & n + 1).Value = TextBoxNotes.Value
sh.Range("U" & n + 1).Value = ComboBoxSellCompany.Value
sh.Range("V" & n + 1).Value = ComboBoxSellTrader.Value
sh.Range("W" & n + 1).Value = TextBoxSellTraderRate.Value
sh.Range("X" & n + 1).Value = ComboBoxSellBroker.Value
sh.Range("Y" & n + 1).Value = TextBoxTimeStamp.Value




Me.TextBoxTradeID.Value = ""
Me.ComboBoxBuyCompany.Value = ""
Me.ComboBoxBuyTrader.Value = ""
Me.TextBoxBuyTraderRate.Value = ""
Me.ComboBoxBuyBroker.Value = ""
Me.ComboBoxProductName.Value = ""
Me.ComboBoxLoc.Value = ""
Me.ComboBoxPipe.Value = ""
Me.ComboBoxPricing.Value = ""
Me.TextBoxTerm.Value = ""
Me.ComboBoxStartDate.Value = ""
Me.ComboBoxEndDate.Value = ""
Me.TextBoxVolume.Value = ""
Me.ComboBoxVolumeType.Value = ""
Me.TextBoxPrice.Value = ""
Me.ComboBoxCurrency.Value = ""
Me.ComboBoxInvoice.Value = ""
Me.ComboBoxContract.Value = ""
Me.ComboBoxGTC.Value = ""
Me.TextBoxNotes.Value = ""
Me.ComboBoxSellCompany.Value = ""
Me.ComboBoxSellTrader.Value = ""
Me.TextBoxSellTraderRate.Value = ""
Me.ComboBoxSellBroker.Value = ""
Me.TextBoxTimeStamp.Value = ""








MsgBox "New Deal is Complete!"










End Sub


Private Sub cmdReset_Click()


Me.TextBoxTradeID.Value = ""
Me.ComboBoxBuyCompany.Value = ""
Me.ComboBoxBuyTrader.Value = ""
Me.TextBoxBuyTraderRate.Value = ""
Me.ComboBoxBuyBroker.Value = ""
Me.ComboBoxProductName.Value = ""
Me.ComboBoxLoc.Value = ""
Me.ComboBoxPipe.Value = ""
Me.ComboBoxPricing.Value = ""
Me.TextBoxTerm.Value = ""
Me.ComboBoxStartDate.Value = ""
Me.ComboBoxEndDate.Value = ""
Me.TextBoxVolume.Value = ""
Me.ComboBoxVolumeType.Value = ""
Me.TextBoxPrice.Value = ""
Me.ComboBoxCurrency.Value = ""
Me.ComboBoxInvoice.Value = ""
Me.ComboBoxContract.Value = ""
Me.ComboBoxGTC.Value = ""
Me.TextBoxNotes.Value = ""
Me.ComboBoxSellCompany.Value = ""
Me.ComboBoxSellTrader.Value = ""
Me.TextBoxSellTraderRate.Value = ""
Me.ComboBoxSellBroker.Value = ""
Me.TextBoxTimeStamp.Value = ""








End Sub
Private Sub cmdExit_Click()
Unload Me




        Exit Sub
End Sub


    






Private Sub UserForm_Click()


End Sub
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

jkpieterse

Well-known Member
Joined
Dec 3, 2007
Messages
1,136
To make the form go away at the click of a button, you need an
VBA Code:
Unload Me
line in that button's click event (as last statement before the End Sub I'd say). If other code needs to use the form's current data, use
VBA Code:
Me.Hide
instead.
To load cell data into a combobox, use code like this:
VBA Code:
ComboBox1.List = Worksheets("Data").Range("A2:A19").Value
 

DirectCommodity

New Member
Joined
Aug 25, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
To make the form go away at the click of a button, you need an
VBA Code:
Unload Me
line in that button's click event (as last statement before the End Sub I'd say). If other code needs to use the form's current data, use
VBA Code:
Me.Hide
instead.
To load cell data into a combobox, use code like this:
VBA Code:
ComboBox1.List = Worksheets("Data").Range("A2:A19").Value
thank you
 

DirectCommodity

New Member
Joined
Aug 25, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
OK I solved Topic #1 for comboBox auto drop down... I went to my excel sheets, defined the column range names for the comboBox rowsource.

Excel Formula "Named Range Combobox" format


=OFFSET(Markets!$A$2, 0, 0, COUNTA(Markets!$A:$A)-1,1)

=OFFSET(Markets!$B$2, 0, 0, COUNTA(Markets!$B:$B)-1,1)

=OFFSET(Markets!$C$2, 0, 0, COUNTA(Markets!$C:$C)-1,1)

I know this is not a solution for VBA, but it's functional and my initial need is solved with just excel formula for now.
 

Forum statistics

Threads
1,143,621
Messages
5,719,795
Members
422,244
Latest member
AYSHANA

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