Using vbYes or If statement
 

I have 3 macros:

One I send as a test to me, one to two of us and the third one to
others.

The code is all the same in the 3 macros apart from one line.

However if I change code in one I have to change it in all three.

Rather than hard coding bu having those 3 lines in the one macro and
silencing two of them, how do I do use an If statement or vbYes or
choose function to select the line of code I would like?

Thanks

Using vbYes or If statement
 

If MsgBox("Select Yes or No", vbYesNo) = vbYes Then
MsgBox "You selected yes."
Else
MsgBox "You selected no."
End If

"Lav" wrote:

I have 3 macros:

One I send as a test to me, one to two of us and the third one to
others.

The code is all the same in the 3 macros apart from one line.

However if I change code in one I have to change it in all three.

Rather than hard coding bu having those 3 lines in the one macro and
silencing two of them, how do I do use an If statement or vbYes or
choose function to select the line of code I would like?

Thanks

Using vbYes or If statement
 

There must be some criteria that determines who gets what code. Just write
that criteria into an If...Then statement.

If criteria1 Then
'do one thing
ElseIf criteria2 Then
'do another
Else
'do something else
End if



"Mike" wrote:

If MsgBox("Select Yes or No", vbYesNo) = vbYes Then
MsgBox "You selected yes."
Else
MsgBox "You selected no."
End If

"Lav" wrote:

I have 3 macros:

One I send as a test to me, one to two of us and the third one to
others.

The code is all the same in the 3 macros apart from one line.

However if I change code in one I have to change it in all three.

Rather than hard coding bu having those 3 lines in the one macro and
silencing two of them, how do I do use an If statement or vbYes or
choose function to select the line of code I would like?

Thanks

Using vbYes or If statement
 

If the one line is different but the rest are the same, write a separate
procedure that contains the same lines and call it in your 3 modules. Change
it in the new macro for the others to update themselves.
Sub Test
'Similar lines
End Sub

Sub Macro1
'Different line
Call Test
End Sub
--
I am running on Excel 2003, unless otherwise stated.


"Lav" wrote:

I have 3 macros:

One I send as a test to me, one to two of us and the third one to
others.

The code is all the same in the 3 macros apart from one line.

However if I change code in one I have to change it in all three.

Rather than hard coding bu having those 3 lines in the one macro and
silencing two of them, how do I do use an If statement or vbYes or
choose function to select the line of code I would like?

Thanks

Using vbYes or If statement
 

The line that changes is part of a Set WB = Workbooks.Add C:...
this is a workbook that considers an email list.

This is used to send emails.

I had thought of using the msgbox with vbYes and vbNo but I need 3
options, not 2. Hence the If crtieria, seems a better option.

The trouble is the 'Set' issue as it is not a piece of code that I
thought does anything, it needs to be part of something else.

Yes calling up a different macro I don't feel is handy as I don't wish
to tweak three lots of code, I'd rather tweak the one set, as apart
from one line the macros are all the same.

Using vbYes or If statement
 

Hi JLGWhiz,

How do I use a criteria so for If Critiera 1, the criteria that needs
to be met would be, something like:

If Msgbox("Sending to Simon", vbYes)

Call SimonEmailMacro

ElseIf MsgBox("Send to Simon and proofreader", vbYes)

Call SimonandOtherMacro

Else MsgBox ("Send to External",vbYes)

Call ExternalMac

End If

Using vbYes or If statement
 

How do you make the decision which to send to whom? Can that be translated
into code? There must be something that is unique about the intended
recipient that can be used as a criteria for determining which version they
get. That is the key to the whole thing. Once you can identify that, the
rest is simple.

"Lav" wrote:

Hi JLGWhiz,

How do I use a criteria so for If Critiera 1, the criteria that needs
to be met would be, something like:

If Msgbox("Sending to Simon", vbYes)

Call SimonEmailMacro

ElseIf MsgBox("Send to Simon and proofreader", vbYes)

Call SimonandOtherMacro

Else MsgBox ("Send to External",vbYes)

Call ExternalMac

End If

Using vbYes or If statement
 

Hi JLGWhiz

I had thought of using data validation and the user using a drop
down.

Then on that cell declare it (as a string?) for the file path (which
is the single like that needs to be changed then set that path.

E.g.

I have an email distribution list and I test the sending of it
with .Sendmail and the array of whom I send to is a Range.Value of
ranges on that list.

To check over the sheets I send I email the items to myself using all
those ranges as my email address.

The second version is with me and a friend

The third is to other people. If I'm not in I need others to flow the
steps without going into code or changing the distribution list sheet
which the macro reads.

So I have cell C22 as a validation list and that is a drop down of
named hyperlinks for the 3 different distribution lists.

Set Emaillist = Range("C22") ' C22 is the cell where the user selects
the file path
Set MyWBDistrbutionList = Workbooks.Add("Emaillist") ' or
workbooks.Open this is the file path that is used in detail later the
Sendmails

The validation list (cell C22) is on the macro page. Can I/How can I
call this to use it in the macro?