Helpful userform tricky to program

[CB Hamlyn]

As an added benefit to my users, I thought I'd be a nice guy and add in a
City, State and Zip browser, where the user picks the state, then the city,
then the zip out of comboboxes. Then, once found, can add any piece
separately to the section of the program they are in.

The problem is there are several places I want to use this, so there are
several places in which the user could be sending the information found to.

For instance I have a "License" userform in which the user enters all their
personal/company information (or in some cases, the sales manager enters
their clients company information so they can complete orders for said
company). In another example, I have a customer information worksheet in
which the user would enter the delivery address of his clients.

So, in short, I need to be able to pass my City/State/Zip userform three
variables that dictate where the user wants to send the City, the State and
the Zip once they've found them. It has to be able to send to either cells
or text fields on a userform.

Any suggestions are greatly appreciated!
Thank you
CB Hamlyn

 

[Ed]

One thing you might try is to create a CustomDocumentProperty and write your
City/State/Zip string there, then call it that info from where you need it -
three separate pulls rather than trying to push three different directions.
You might also be able to use whatever event calls this form to set an If -
Else If or SelectCase with three different CustomDocumentProperty fields
("If this event called the form, write Form info to CustDocProp1; ElseIf the
other event called the form, write info to CustDocProp2 . . . etc"). That
could eliminate the chance of a refresh overwriting the previous property
info with new selected info.

HTH
Ed

 

[Stephen Bullen]

Hi CB,

So, in short, I need to be able to pass my City/State/Zip userform three
variables that dictate where the user wants to send the City, the State and
the Zip once they've found them. It has to be able to send to either cells
or text fields on a userform.

Don't do it like that. Instead, add those as properties of the form and
instead of closing it, hide it:

Dim mbOK As Boolean 'Whether the user OK'd or Cancelled

Private Sub cmdOK_Click()
mbOK = True
Me.Hide
End Sub

Private Sub cmdCancel_Click()
mbOK = False
Me.Hide
End Sub

'Using the [x] to close the form is the same as Cancel
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
cmdCancel_Click
Cancel = True
End If
End Sub

'Return the form's selections
Public Property Get State() As String
State = cboState.Text
End Sub

Public Property Get City() As String
City = cboCity.Text
End Sub

Public Property Get ZIP() As String
ZIP = cboZIP.Text
End Sub


Then in the calling code, you show the form, read the values and put them
wherever you want to - i.e. the calling code decides what to do with the
results:

Private Sub cmdChooseZIP_Click()

Dim oZipForm As frmZIPForm

Set oZipForm = New frmZipForm

oZipForm.Show

If oZipForm.OK Then
Range("A1").Value = oZipForm.ZIP
End If

End Sub



Regards

Stephen Bullen
Microsoft MVP - Excel

Professional Excel Development
The most advanced Excel VBA book available
www.oaltd.co.uk/ProExcelDev

 

[CB Hamlyn]

Thank you. This is more or less what I did. I made some public variables
for the results, then have the calling code use them as appropriate.

Thank you for the suggestions
CB Hamlyn

Hi CB,

So, in short, I need to be able to pass my City/State/Zip userform three
variables that dictate where the user wants to send the City, the State
and
the Zip once they've found them. It has to be able to send to either
cells
or text fields on a userform.

Don't do it like that. Instead, add those as properties of the form and
instead of closing it, hide it:

Dim mbOK As Boolean 'Whether the user OK'd or Cancelled

Private Sub cmdOK_Click()
mbOK = True
Me.Hide
End Sub

Private Sub cmdCancel_Click()
mbOK = False
Me.Hide
End Sub

'Using the [x] to close the form is the same as Cancel
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
cmdCancel_Click
Cancel = True
End If
End Sub

'Return the form's selections
Public Property Get State() As String
State = cboState.Text
End Sub

Public Property Get City() As String
City = cboCity.Text
End Sub

Public Property Get ZIP() As String
ZIP = cboZIP.Text
End Sub


Then in the calling code, you show the form, read the values and put them
wherever you want to - i.e. the calling code decides what to do with the
results:

Private Sub cmdChooseZIP_Click()

Dim oZipForm As frmZIPForm

Set oZipForm = New frmZipForm

oZipForm.Show

If oZipForm.OK Then
Range("A1").Value = oZipForm.ZIP
End If

End Sub



Regards

Stephen Bullen
Microsoft MVP - Excel

Professional Excel Development
The most advanced Excel VBA book available
www.oaltd.co.uk/ProExcelDev