User Form Help required please

[cmerrick]

Hi all,

So I've recently started to use VBA and I need some help please.

I have a VBA code that records details that a user completes on a USERFORM and puts the into 'sheet 2' on a table.

My User form consists of text boxes, check boxes and an option button.

Everything works fine however there is something very annoying occurring and I can't find a solution.

If textbox1 is left blank and the check boxes are completed this data is logged on sheet 2, fine (lets call this Data Cap 1) . But when you create a new entry, if textbox1 has info keyed in, the entry (Which should be data Cap 2 on the next row down) populates the same row as Data Cap1.

Can anyone help me. Happy to provide code etc.. just not sure which part to send over.

 

[Myrko]

I believe the row where new entry should be populated is not aimed in a right way. Can you share the code where your macro writes the data to Sheet 2?

 

[Peter h]

Do you have a "Submit" button? What action do you use to send the data in your userform to your sheet? Post the code for your button click please, and we should be able to help you.

 

[cmerrick]

I have

Private Sub Okbutton_Click()
Unload NewEnquiryForm
MsgBox "Your entry has been created"
Dim emptyRow As Long
'Make Sheet2 active
 Sheet2.Activate
 
 'Determine emptyRow
 emptyRow = WorksheetFunction.CountA(Range("b:b")) + 2
 
 'Transfer information
 Cells(emptyRow, 2).Value = brokernameinput.Value
 Cells(emptyRow, 3).Value = telephoneinput.Value
 Cells(emptyRow, 4).Value = emailinput.Value
 Cells(emptyRow, 5).Value = Timeinput.Value
 Cells(emptyRow, 6).Value = Dateinput.Value
 Cells(emptyRow, 11).Value = Additionaldetails.Value
 
If enqoption1.Value = True Then Cells(emptyRow, 7).Value = "Yes"
If enqoption2.Value = True Then Cells(emptyRow, 8).Value = "Yes"
If enqoption3.Value = True Then Cells(emptyRow, 9).Value = "Yes"
 
If callbackyes.Value = True Then
     Cells(emptyRow, 10).Value = "Yes"
Else
     Cells(emptyRow, 10).Value = "No"
     
End If
End Sub

 

[Norie]

Is there any field that's always going to be populated?

 

[cmerrick]

Is there any field that's always going to be populated?

There are two fields that will populate by default unless the User changes the default response.

 

[cmerrick]

There are two fields that will populate by default unless the User changes the default response.

Actually, I also have a Date and Time Stamp that will carry over so there are actually 4 fields that will populate

 

[Peter h]

I have

Private Sub Okbutton_Click()
Unload NewEnquiryForm
MsgBox "Your entry has been created"
Dim emptyRow As Long
'Make Sheet2 active
 Sheet2.Activate
 
 'Determine emptyRow
 emptyRow = WorksheetFunction.CountA(Range("b:b")) + 2
 
 'Transfer information
 Cells(emptyRow, 2).Value = brokernameinput.Value
 Cells(emptyRow, 3).Value = telephoneinput.Value
 Cells(emptyRow, 4).Value = emailinput.Value
 Cells(emptyRow, 5).Value = Timeinput.Value
 Cells(emptyRow, 6).Value = Dateinput.Value
 Cells(emptyRow, 11).Value = Additionaldetails.Value
 
If enqoption1.Value = True Then Cells(emptyRow, 7).Value = "Yes"
If enqoption2.Value = True Then Cells(emptyRow, 8).Value = "Yes"
If enqoption3.Value = True Then Cells(emptyRow, 9).Value = "Yes"
 
If callbackyes.Value = True Then
     Cells(emptyRow, 10).Value = "Yes"
Else
     Cells(emptyRow, 10).Value = "No"
     
End If
End Sub

I have never used that line of code to find the first empty row before, so I'm not familiar with it, but it looks like your code is entering data in the first empty row in column B. Are there any blank Cells in Column B? From your original post, I'm left to assume that TextBox1 was the "brokernameinput" Textbox in your code, is this correct to assume? If it is, then this textbox will not always have data in it, and will leave the cell in "B" blank, correct? From your description in the original post, and the code you provided, the only column that will always have data entered into it is "J" with a "Yes" or "No", correct? If I'm correct in my assumption, what I would do is change how the emptyrow is written to this:

Private Sub Okbutton_Click()
Unload NewEnquiryForm
MsgBox "Your entry has been created"
Dim emptyRow As Long
'Make Sheet2 active
 Sheet2.Activate
 
 'Determine emptyRow
 emptyRow = [COLOR=#FF0000]Sheets(2).Range("J" & Rows.Count).End(xlUp).Row + 1[/COLOR]
 
 'Transfer information
 Cells(emptyRow, 2).Value = brokernameinput.Value
 Cells(emptyRow, 3).Value = telephoneinput.Value
 Cells(emptyRow, 4).Value = emailinput.Value
 Cells(emptyRow, 5).Value = Timeinput.Value
 Cells(emptyRow, 6).Value = Dateinput.Value
 Cells(emptyRow, 11).Value = Additionaldetails.Value
 
If enqoption1.Value = True Then Cells(emptyRow, 7).Value = "Yes"
If enqoption2.Value = True Then Cells(emptyRow, 8).Value = "Yes"
If enqoption3.Value = True Then Cells(emptyRow, 9).Value = "Yes"
 
If callbackyes.Value = True Then
     Cells(emptyRow, 10).Value = "Yes"
Else
     Cells(emptyRow, 10).Value = "No"
     
End If
End Sub

Give that a try, and let us know what happens.

 

[cmerrick]

I have never used that line of code to find the first empty row before, so I'm not familiar with it, but it looks like your code is entering data in the first empty row in column B. Are there any blank Cells in Column B? From your original post, I'm left to assume that TextBox1 was the "brokernameinput" Textbox in your code, is this correct to assume? If it is, then this textbox will not always have data in it, and will leave the cell in "B" blank, correct? From your description in the original post, and the code you provided, the only column that will always have data entered into it is "J" with a "Yes" or "No", correct? If I'm correct in my assumption, what I would do is change how the emptyrow is written to this:

Private Sub Okbutton_Click()
Unload NewEnquiryForm
MsgBox "Your entry has been created"
Dim emptyRow As Long
'Make Sheet2 active
 Sheet2.Activate
 
 'Determine emptyRow
 emptyRow = [COLOR=#ff0000]Sheets(2).Range("J" & Rows.Count).End(xlUp).Row + 1[/COLOR]
 
 'Transfer information
 Cells(emptyRow, 2).Value = brokernameinput.Value
 Cells(emptyRow, 3).Value = telephoneinput.Value
 Cells(emptyRow, 4).Value = emailinput.Value
 Cells(emptyRow, 5).Value = Timeinput.Value
 Cells(emptyRow, 6).Value = Dateinput.Value
 Cells(emptyRow, 11).Value = Additionaldetails.Value
 
If enqoption1.Value = True Then Cells(emptyRow, 7).Value = "Yes"
If enqoption2.Value = True Then Cells(emptyRow, 8).Value = "Yes"
If enqoption3.Value = True Then Cells(emptyRow, 9).Value = "Yes"
 
If callbackyes.Value = True Then
     Cells(emptyRow, 10).Value = "Yes"
Else
     Cells(emptyRow, 10).Value = "No"
     
End If
End Sub

Give that a try, and let us know what happens.

All of your assumptions are correct.

I replaced the code and it seems to work perfectly.

While you are here... Would you mind maybe assisting on another issue?

I have trawled the net trying to find a code to remove the last row of data that has been transferred to sheet 2 (in case a user creates an entry by mistake) however all the examples I have found either clear the data entirely or want a specific range. There wouldn't be a specific range in the code I require as the new entry could be in any row depending on how full the table is.

Kindest regards and thanks for your help.

 

[Peter h]

All of your assumptions are correct.

I replaced the code and it seems to work perfectly.

While you are here... Would you mind maybe assisting on another issue?

I have trawled the net trying to find a code to remove the last row of data that has been transferred to sheet 2 (in case a user creates an entry by mistake) however all the examples I have found either clear the data entirely or want a specific range. There wouldn't be a specific range in the code I require as the new entry could be in any row depending on how full the table is.

Kindest regards and thanks for your help.

Usually, you should start a new thread for a new problem, but I'll go ahead and give you the code to do what you're wanting. So if you have a second button in your userform to delete the last entry, paste this code in the click sub.

Dim ws As Worksheet
Dim lRow As Long
Set ws = Thisworkbook.Sheets(2)
lRow = ws.Range("J" & Rows.Count).End(xlUp).Row
Msg1 = msgbox("You are about to delete the last entry. Do you want to continue?" & vbNewLine & "Click yes to continue, or no to cancel.", vbYesNo)
If Msg1 = vbNo Then Exit Sub
ws.Range("A" & lRow).entirerow.delete
End Sub

I typed this on my phone, so try it out and let me know if it works ok.