PDA

View Full Version : Select or If Statement?



samohtwerdna
11-28-2005, 10:31 AM
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:
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

Is there a better way to write this condition ??

Hopefully this question makes sense.

Bob Phillips
11-28-2005, 10:45 AM
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:
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

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?

mvidas
11-28-2005, 10:46 AM
Hi Andrew,

There is a better way to do it, using a Select Case statement: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 SubAlso, 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

samohtwerdna
11-28-2005, 10:54 AM
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:thumb

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?

malik641
11-28-2005, 10:55 AM
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:

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

That's how I would do it, anyway (I love named ranges....until convinced otherwise)

samohtwerdna
11-28-2005, 10:56 AM
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?

mvidas
11-28-2005, 11:20 AM
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:txtEmail.Text = Sheets("Addresses").Range(ActiveCell.Offset(-1, -10).Text)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: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 FunctionYou 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: Dim RW As Range
Set RW = Rows(3)
MsgBox RW.Columns("HA").Address(0, 0)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

samohtwerdna
11-28-2005, 11:59 AM
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??

Bob Phillips
11-28-2005, 12:37 PM
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

mvidas
11-28-2005, 01:07 PM
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 :)