VBA userforms

[ccordner]

I have a macro and a couple of userforms which works thus:

1) User enters information about sickness into a userform

2) Clicking okay, the userform does some validation checks to make sure they haven't missed any compulsory fields.

3) If everything is okay, it transfers this info to another userform, which is used to compose an email to distribute this information to the relevant supervisors.

The problem I have is that if everything isn't working okay, I need a way of passing that information back into the subroutine that called the form, so that it can halt the code before it closes the form (and brings up the second one).

I thought I had solved this.

The form is called:

frmReportSickness.Show

And I inserted on the 'OK' button the code:

If ResumptionValid = False Then
  frmReportSickness.Tag = 1
  Unload Me
  Exit Sub
  Else
  frmReportSickness.Tag = 0
End if

But when the system returns to the subroutine, the value stored in the 'Tag' property has disappeared and frmReportSickness.Tag is a "null" value.

Any ideas why? Or how I could do this better? I would prefer not to resort to sticking variables in random cells if possible.

Chris

 

[JamesW]

Hi,

How about something like:

Private Sub CommandButton1_Click()
    If ResumptionValid = False Then
        MsgBox "Some data is incorrect, please check again"
    Else
        DoStuff 'Add stuff to other form, or whatever you want to do if it's all fine
    End If
End Sub

If the data is incorrect, it wont do anything and it'll allow the user to edit the form

 

[ccordner]

The main reason I wasn't going to do it that way was because there is a reasonably chunk of code, with a few more 'If's and loops, so I was hoping I could pass the info back somehow. If not, it means I have to get my head around another algorithm.

Is there not a way of doing that then?

 

[ccordner]

Thanks for your help with this one. Just discovered an "Unload me" command, which was causing the problems.

Having said that, I have done it your way which has actually allowed me to get rid of some duplicate code.

Chris