Results 1 to 14 of 14

Thread: Data Changes

  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Changes

    I've got a huge list of offices, each in the following format:

    Office Name - Location

    Is there a way to (in VBA) separate the Office Name and location for display purposes? I have a combo box for the office name, and another for the location, I would like to be able to select an office name, and have the location populate the second box automatically, however where there are multiple offices listed, each location would be displayed. (I.E. everything after the "-" in the office name.)

    Any ideas?

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Changes

    I'm not real clear on what you are asking. If you are asking how split the Office Name - Location into two strings, then the following will do this if the combination is in variable strNameLocation:

    <pre>Dim strName as String, strLocation as String
    strName = Trim(Left(strNameLocation, InStr(strNameLocation, "-") - 1))
    strLocation = Trim(Right(strNameLocation, Len(strNameLocation) _
    - InStr(strNameLocation, "-") - 1))
    </pre>

    Legare Coleman

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Changes

    That's it Exactly.. Thanks again Legare!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Changes

    Okay... so i've got some 860+ offices, can you think of a way to display only the office name in one combobox, (the rowsource of which is offices!L2:L860) and the location in another?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Changes

    Assuming the Name - Location is in Column A, starting in A1, put the following formula in the top cell in an empty column and fill it down:

    <pre>=TRIM(LEFT(A1,FIND("-",A1)-1))
    </pre>


    Fill this formula down another empty column:

    <pre>=TRIM(RIGHT(A1,LEN(A1)-FIND("-",A1)-1))
    </pre>


    You can hide these columns if desired. Bind your combo boxes on these columns.
    Legare Coleman

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Changes

    <img src=/S/bow.gif border=0 alt=bow width=15 height=15> Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Changes

    Say Legare,

    Perhaps you can think of way to:

    Limit the Location to only applicable offices? If I have the Office of Smith & Doe, with locations in Albuquerque, Santa Fe, Roswell, and Carlsbad, is there any way to limit my 'location' combo boxes to just this data?

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Changes

    Limit it to just what data? What are the criteria for making the decision that you want to limit it?
    Legare Coleman

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Changes

    Limit only to those four locations... as opposed to the default of all office locations for all offices...

    The criteria would be the Office name.. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Changes

    Only do it for that one office name, and always do it? How do I know when you want to do it for that office name and not do the default?
    Legare Coleman

  11. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Changes

    Because I will select the office name from a combo box on my form.

    Offices and Clients are required. For every call, there will always be an office selected, but how can I make my location box reflect the office selected? (As you recall, the format is "Office Name - Location")
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Changes

    I am having a really hard time understanding what you are asking. If the format is Office - Location, and an office is always selected, then isn't the location already reflected?

    Or, are you saying that you want to have two combo boxes, and when the user selects the office from the first one, then you want the locations to appear in the second one? If so, you should be able to do that fairly simply by using the Change Event routine in the Office Combo Box to populate the Location Combo Box.
    Legare Coleman

  13. #13
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Changes

    Sorry.. perhaps i'm unclear..

    What process can I use to update the rowsource property with only the pertinent information?

    With the office of Smith and doe, we had four locations. Albuquerque, Santa Fe, Roswell, & Carlsbad. These offices are listed as follows:

    Smith & Doe - Albuquerque
    Smith & Doe - Santa Fe
    Smith & Doe - Roswell
    Smith & Doe - Carlsbad

    If the offices of Smith & Doe are selected in cbOffice, than I want cbLocation to populate with only the four location options. The problem i'm having is determining (in VBA) that these locations apply only to the offices of Smith & Doe. (Essentially, I want Smith & Doe to be the criteria for which locations are displayed...)

    Thanks again Legare!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Changes

    Assuming that the cells containing the Office - Location are sorted, and that an office name can not contain a "-", you can write code to search through the cells something like this:

    <pre>Dim oCurCell As Range, oFirst As Range, oLast As Range
    For Each oCurCell In Range("Table")
    If UCase(Trim(Left(oCurCell, InStr(oCurCell, "-") - 1))) = _
    UCase(cbOffice.Value) Then
    If oFirst Is Nothing Then
    Set oFirst = oCurCell
    Set oLast = oCurCell
    Else
    Set oLast = oCurCell
    End If
    Else
    If Not oFirst Is Nothing Then
    Exit For
    End If
    End If
    Next oCurCell
    cbLocations.RowSource = oFirst.Address & oLast.Address
    Set oFirst = Nothing
    Set oLast = Nothing
    </pre>


    The above is just to get you started. Since I do not have a workbook set up with the table and the form, the above was not tested.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •