Have a problem using the refresh button only when initializing User Form.

Question

I'm trying to finishing this project up. I have several text boxes on my User Form that are required to be filled. This user form sends data to a database which ends up part of a pivot table. All is great. However, one of the command buttons I have is a refresh button. Though I have not put anything in the form. I get the error code as if I skipped over the required text box. If I enter a date and select my "reset" cmd option then select "refresh" it seems to be okay. If I open the form up and select "reset" without first putting a date in, I get an error message. Sometimes a person may want to refresh the pivot table without actually putting data on the form. I'm keeping as much off the tables as possible, so everything is done on the virtual form. The code I'm using is fine; now I'm only hoping I can get this code ignored only if the "refresh" command is used. I thought I could us a "Call Refresh" but I think I would still get the error after the tables were refreshed.

Thank you,

Private Sub TxtDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)

If Trim(TxtDate.Value) = "" And Me.Visible Then

MsgBox "Please enter date in dd mmm yy format.", vbCritical, "Error"

Cancel = True

InboundForm.TxtDate.SetFocus

TxtDate.BackColor = vbYellow

Else

TxtDate.BackColor = vbWhite

End If

End Sub

Answer 1

It begins with Dim and then it has a another code "Sub Reset..." is that to go into a module?

Integrate it into the code module of your Userform please, the idea behind:

The variable ResetButtonClicked is declared "at the top of the module" which means each sub into your form can read/write the variable at any time.

You said you click the reset button and got an error in TxtDate_Exit, so if the button is clicked we write TRUE into the variable.

What ever you have in your code, the change event of the textbox TxtDate fires, the VBA event manager interrupt the code of the command button and executes TxtDate_Exit.

In TxtDate_Exit we check the state of ResetButtonClicked and do nothing if it is true.

The sub ends and the event manager lets your button's code continue to run.

We write FALSE into the variable and the code in TxtDate_Exit works as before.

That's it.

Andreas.

Answer 2

Thank you for the quick response. I'm slightly confused. I see you we wrote my code represented in your later code. The first part of your answer is that in addition? It begins with Dim and then it has a another code "Sub Reset..." is that to go into a module?

Thank you,

Answer 3

Try something as shown below.

Andreas.

Dim ResetButtonClicked as Boolean

sub ResetButton_Click()

ResetButtonClicked = true

'Your code here

DoEvents

  ResetButtonClicked = false

end sub

Private Sub TxtDate_Exit(ByVal Cancel As MSForms.ReturnBoolean) 

if ResetButtonClicked then exit sub

If Trim(TxtDate.Value) = "" And Me.Visible Then 

MsgBox "Please enter date in dd mmm yy format.", vbCritical, "Error" 

Cancel = True 

InboundForm.TxtDate.SetFocus 

TxtDate.BackColor = vbYellow 

Else 

TxtDate.BackColor = vbWhite 

End If 

End Sub