IF with VBA

rharri1972

Board Regular
Joined
Nov 12, 2021
Messages
125
Office Version
  1. 2019
Platform
  1. Windows
Hello! So I have been trying to learn as I go and ask "how to's" with each step thinking it would be easiest but in turn I think I have just aggravated a couple of people which was NEVER my intent. My entire goal is to build a database for my company. I kind of understand VBA but it still takes me hours to disect code and there is SO MUCH i have no clue about. So this time I am spilling everything I need per userform and I hope not to over complicate things.

I have a userform "Customer Order". I will manually put in information into this form to process a customers order. It will contain labels, textboxes and combo boxes. I will have a command button "OK".
On the click of the command button, I want to send all data to a worksheet to be captured.

For each customer there is a Customer ID. The worksheet will be named after this Customer ID. (typed in the tab)

Example Customer ID would be like PIL1500

TEXTBOX 1 will contain the Customer ID.

Textbox 1 = Customer ID
Textbox 2= Order Date
Textbox 3 = PO number
ComboBox1 = Customer Name
Textbox 4 = Address (street address)
Textbox 5 = City
Textbox 6= State
Textbox 7=Zip

The worksheet will be the same order Left to right Starting with A2:H2.

Row 1 being left for Column Headers.

would also want to make sure that when executing that the data be placed in the next available row.

Any and all help will be greatly appreciated. There will be more questions to come once i am able to complete this step and then move on to the next userform.

Thanks!
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,986
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
So you have a Sub CommandButton1_Click; consider inserting within its code the following concept:
VBA Code:
Dim myNext As Long, dSh As Worksheet

'Check that the Customer Id sheet exists:
On Error Resume Next
Set dSh = Sheets(Me.TextBox1.Value)
On Error GoTo 0
If dSh Is Nothing Then
    'missing:
    MsgBox ("There is not a sheet with this name: " & Me.TextBox1.Value _
       & vbCrLf & "Correct and retry")
    Me.TextBox1.SetFocus
    Exit Sub
Else
    'it exists:
    With dSh
        myNext = .Cells(Rows.Count, "A").End(xlUp).Row + 1      'get first free row..
        .Cells(myNext, "A") = CDate(Me.TextBox2.Value)          '.. and populate with data from userform
        .Cells(myNext, "B") = Me.TextBox3.Value
    '
    'more transfers here
    '
    End With
End If

Does this matches your request?
 
Solution

rharri1972

Board Regular
Joined
Nov 12, 2021
Messages
125
Office Version
  1. 2019
Platform
  1. Windows
So you have a Sub CommandButton1_Click; consider inserting within its code the following concept:
VBA Code:
Dim myNext As Long, dSh As Worksheet

'Check that the Customer Id sheet exists:
On Error Resume Next
Set dSh = Sheets(Me.TextBox1.Value)
On Error GoTo 0
If dSh Is Nothing Then
    'missing:
    MsgBox ("There is not a sheet with this name: " & Me.TextBox1.Value _
       & vbCrLf & "Correct and retry")
    Me.TextBox1.SetFocus
    Exit Sub
Else
    'it exists:
    With dSh
        myNext = .Cells(Rows.Count, "A").End(xlUp).Row + 1      'get first free row..
        .Cells(myNext, "A") = CDate(Me.TextBox2.Value)          '.. and populate with data from userform
        .Cells(myNext, "B") = Me.TextBox3.Value
    '
    'more transfers here
    '
    End With
End If

Does this matches your request?
Anthony 47 I am going to put this in and see if it works for me. I will update you!
Thanks so much for your help!
 

Forum statistics

Threads
1,175,769
Messages
5,899,382
Members
434,766
Latest member
PremPrakash

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