Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Helpful userform tricky to program
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Helpful userform tricky to program
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 "CB Hamlyn" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Helpful userform tricky to program
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Helpful userform tricky to program
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 "Stephen Bullen" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why is AutoFill helpful | Excel Discussion (Misc queries) | |||
Helpful New Functions: ALL and ANY | Excel Worksheet Functions | |||
tab in userform locks program | Excel Programming | |||
Can I program a userform via spreadsheet? | Excel Programming | |||
Repost: Looking for info on how to program a userform to use filters | Excel Programming |