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

D

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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
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
 
Upvote 0
jkpieterse said:
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
Click to expand...
thank you
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
VBA Code Error
Replies
3
Views
269
dreid1011
dreid1011
Agnarr
  • Question
VBA problem with creating new sheets
Replies
0
Views
338
Agnarr
Agnarr
SemperFi
  • Solved
VBA Form TextBox needs formatting
Replies
1
Views
322
SemperFi
SemperFi
X
  • Solved
When update the record the entries are saved in the columnheaders ^^
Replies
2
Views
289
xainthowell
X
X
  • Solved
Displayed numeric date instead of the entered format date
Replies
6
Views
333
hicksi
hicksi

Forum statistics

Threads
1,214,651
Messages
6,120,742
Members
448,989
Latest member
mariah3

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