Consulting

Results 1 to 10 of 10

Thread: Select or If Statement?

  1. #1
    VBAX Contributor samohtwerdna's Avatar
    Joined
    Dec 2004
    Location
    Denver Colorado
    Posts
    143
    Location

    Select or If Statement?

    Hello Again!

    I'm almost done with this little application. One thing I should know how to do (But don't) is chane the value of a text based on another cell value.

    I have a "Dealer" column with about 27 dealer abbrv. like "ALK", "DGN" and so on. I want to set the email based on the value in this cell when My user form intitalizes I really don't want to do this:
    [VBA]Private Sub UserForm_Initialize()

    If ActiveCell.Offset(-1, -10) = "ALK" Then
    txtEmail.Text = "john@domain1.com"
    ElseIf ActiveCell.Offset(-1, -10) = "CLK" Then
    txtEmail.Text = "rob@domain2.com"
    ElseIf ActiveCell.Offset(-1, -10) = "DDT" Then
    txtEmail.Text = "regina@domain3.com"
    ElseIf ActiveCell.Offset(-1, -10) = "DGN" Then
    txtEmail.Text = "Natasha@domain4.com"
    end if
    end sub
    [/VBA]
    Is there a better way to write this condition ??

    Hopefully this question makes sense.
    To live is Christ... To code is cool!

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    Quote Originally Posted by samohtwerdna
    Hello Again!

    I'm almost done with this little application. One thing I should know how to do (But don't) is chane the value of a text based on another cell value.

    I have a "Dealer" column with about 27 dealer abbrv. like "ALK", "DGN" and so on. I want to set the email based on the value in this cell when My user form intitalizes I really don't want to do this:
    [VBA]Private Sub UserForm_Initialize()

    If ActiveCell.Offset(-1, -10) = "ALK" Then
    txtEmail.Text = "john@domain1.com"
    ElseIf ActiveCell.Offset(-1, -10) = "CLK" Then
    txtEmail.Text = "rob@domain2.com"
    ElseIf ActiveCell.Offset(-1, -10) = "DDT" Then
    txtEmail.Text = "regina@domain3.com"
    ElseIf ActiveCell.Offset(-1, -10) = "DGN" Then
    txtEmail.Text = "Natasha@domain4.com"
    end if
    end sub
    [/VBA]
    Is there a better way to write this condition ??

    Hopefully this question makes sense.
    Not much better if you are going to do it in code, but why not hold a table of codes, emails, and use a VLOOKUP on the worksheet to get the email addy, and use that in the form?

  3. #3
    Knowledge Base Approver
    The King of Overkill!
    VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    Hi Andrew,

    There is a better way to do it, using a Select Case statement:[vba]Private Sub UserForm_Initialize()
    'Word of advice: using an activecell as a reference isn't a great idea
    Select Case ActiveCell.Offset(-1, -10).Text
    Case "ALK": txtEmail.Text = "john@domain1.com"
    Case "CLK": txtEmail.Text = "rob@domain2.com"
    Case "DDT": txtEmail.Text = "regina@domain3.com"
    Case "DGN": txtEmail.Text = "Natasha@domain4.com"
    End Select
    End Sub[/vba]Also, I've edited the email addresses in your original question, so as to avoid the spiders that harvest them (and prevent spam for those recipients, even if they're not valid it could save some spam for the domain)
    Matt

  4. #4
    VBAX Contributor samohtwerdna's Avatar
    Joined
    Dec 2004
    Location
    Denver Colorado
    Posts
    143
    Location
    Matt - Thanks again for the Help!
    Bob - I like your suggestion too and will play around with that.

    The Select Case satement is really what I was looking for - I just couldn't remember the syntax.

    I felt funny about putting those address in my post - Thanks for cleaning it up

    Anyway, I found away around the "Outlook Object Model Gaurd " Using a different object (in my case CDO) to send the mail - I thought this might make a good knowledge base entry, what do you think?
    To live is Christ... To code is cool!

  5. #5
    Administrator
    2nd VP-Knowledge Base VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    Maybe you could create a list on a hidden sheet with all the abbreviations and adjacent to that would be the email address (Edit: Similar to what Bob said). You could then name that (dynamically, of course) to "abbr_list" and then your code would look like:

    [VBA] Private Sub UserForm_Initialize()
    Dim cell As Variant

    For Each cell In Range("abbr_list")
    If cell.Text = ActiveCell.Offset(-1, 10).Text Then
    txtEmail.Text = cell.Offset(0, 1).Text 'The adjacent email address to the abbreviation
    End If
    Next cell
    End Sub
    [/VBA]
    That's how I would do it, anyway (I love named ranges....until convinced otherwise)




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  6. #6
    VBAX Contributor samohtwerdna's Avatar
    Joined
    Dec 2004
    Location
    Denver Colorado
    Posts
    143
    Location
    Oh, Matt, I forgot to ask -

    Why is using an activecell a bad Idea?

    What would be better for determining the contents of a particular order entry if all the contents are on the same row?
    To live is Christ... To code is cool!

  7. #7
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    I do agree with Bob and Joseph, especially Joseph's idea of having named ranges but with a different spin on it. If you had range names of "ALK", "CLK", "DDT", etc, you would only need:[vba]txtEmail.Text = Sheets("Addresses").Range(ActiveCell.Offset(-1, -10).Text)[/vba]This would produce a lot less code, though it could error if not written right when a range name doesn't exist.

    Another way would be to have a function with the codes/addresses in it, like a vlookup table in vba, but still using the select case method:[vba]Private Sub UserForm_Initialize()
    txtEmail.Text = LookupAddress(ActiveCell.Offset(-1, -10).Text)
    End Sub
    Function LookupAddress(ByVal CellText As String) As String
    Select Case CellText
    Case "ALK": LookupAddress = "john@domain1.com"
    Case "CLK": LookupAddress = "rob@domain2.com"
    Case "DDT": LookupAddress = "regina@domain3.com"
    Case "DGN": LookupAddress = "Natasha@domain4.com"
    End Select
    End Function[/vba]You could use this in other parts of your routine as well, if you ever need it outside of _Initialize

    The two reasons I stay away from ActiveCell are: a) I don't like to use .Select statements in my code, and b) I don't like to trust that the user (even if its me) won't change the activecell prior/during runtime, and your offset could cause an error if it isn't in the correct column (anything column A:J would cause an error due to the -10 columns). If the cells you need are in a specific row, just use a range variable to refer to that row, and the subsequent cells can be called from within it:[vba] Dim RW As Range
    Set RW = Rows(3)
    MsgBox RW.Columns("HA").Address(0, 0)[/vba]You normally wouldn't use the .Address property, you would probably want to use the .Text property instead, but I just put that there as an example.
    Matt

  8. #8
    VBAX Contributor samohtwerdna's Avatar
    Joined
    Dec 2004
    Location
    Denver Colorado
    Posts
    143
    Location
    If I understand correctly, I should have a table consisting of two columns somwhere on my spreadsheet. One column for the abrv. and the adjacent column for the email addresses. Then I would make this table a named range maybe "address" then my code for selecting the email address for the specific job order would be
    txtEmail.Text = Sheets("Addresses").Range(ActiveCell.Offset(-1, -10).Text)
    And if I wanted to be on the safe side - I would first set my Row and then work from there. In you example RW = Row(3) - If I wanted to set RW to the current Row I'm thinking I would use ActiveCell? then I'm back to the same problem I think? - I must not be understanding this fully, but my spreadsheet records job info from several different users. Each job is entered on one row - Half of the spreadsheet is just for user entry then the right half dose calculations to determine production schedule and ship date info - So I have a sort function that takes the first half and sorts it properly based on approved jobs for production. (So my jobs move around a little) So I guess I don't understand How to set which Row I want to concider without basing it of of the row that the user is on/ filling out??
    To live is Christ... To code is cool!

  9. #9
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    Quote Originally Posted by samohtwerdna
    If I understand correctly, I should have a table consisting of two columns somwhere on my spreadsheet. One column for the abrv. and the adjacent column for the email addresses. Then I would make this table a named range maybe "address" then my code for selecting the email address for the specific job order would be
    txtEmail.Text = Sheets("Addresses").Range(ActiveCell.Offset(-1, -10).Text)
    No that is for many range names. If you just have one range name, it would probably be

    With Worksheets("Addresses")
    txtEmail.Text = Application.VLOOKUP(.ActiveCell.Offset(-1,-10).Text),.Ranges("address"),2,False)
    End With

  10. #10
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    Quote Originally Posted by samohtwerdna
    In you example RW = Row(3) - If I wanted to set RW to the current Row I'm thinking I would use ActiveCell? then I'm back to the same problem I think? - I must not be understanding this fully, but my spreadsheet records job info from several different users. Each job is entered on one row - Half of the spreadsheet is just for user entry then the right half dose calculations to determine production schedule and ship date info - So I have a sort function that takes the first half and sorts it properly based on approved jobs for production. (So my jobs move around a little) So I guess I don't understand How to set which Row I want to concider without basing it of of the row that the user is on/ filling out??
    I may not be understanding the flow of your workbook...
    Does a user enter information on a row, and when a certain number of fields are filled in on the row, the userform is shown based on _selectionchange or something? If not, when/how is this called?
    It is hard to good advice without knowing how your workbook works. There are some occasions when using ActiveCell is necessary, though I can't think of any at the moment. But in any case, I can't really give you an idea of how to set the row variable (or any changes) without knowing what exactly you're doing. All I could do is give you the syntax for the Select Case statement, and explain that I don't think using ActiveCell is a good idea

Posting Permissions

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