Calling a macro from an If statement

K

Kathryn

I want to run a macro based on one or both value(s) of 2
checkboxes else I want to skip the macro. If chkbx1 is
TRUE, I want to skip the macro. If chkbx2 is TRUE, I want
to skip to skip the macro. If chkbx1 and chkbx2 are both
TRUE, I want to skip the macro.

This is what I have and it is not working (E6 and E7 are
the cells linked to the checkbox controls....

Sub test()
Worksheets("Data").Select
Range("E6").Select
If E6 = "TRUE" Then
Worksheets("Form").Select
Range("A4:I4").Select
ElseIf E7 = "TRUE" Then
Worksheets("Form").Select
Range("A4:I4").Select
Else
mcrSaveAs
End If

End Sub

Thanks for any advice you may have!
 
T

Trev

Hi Kathryn,
I believe that your code is looking for the word 'True' in
the cell. True is really minus 1 and false is zero.
I havn't tried it but you could try omitting the speech
marks from around "True" which I think Excel will then
interpret correctly

trev
 
B

Bob Phillips

Kathryn,

Try this

With Worksheets("Data")
If .CheckBoxes("Check Box 1").Value = xlOn Or .CheckBoxes("Check Box
2").Value = xlOn Then
Worksheets("Form").Select
Range("A4:I4").Select
Else
mcrSaveAs
End If
End With

which doesn't use the linked cells, or

With Worksheets("Data")
If .Range("E6") Or .Range("E7") Then
Worksheets("Form").Select
Range("A4:I4").Select
Else
mcrSaveAs
End If
End With

which does.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
K

Kathryn

Thank you so much for your response. I tested the code,
and it still wasn't calling the macro. Trev reminded me
that the "TRUE" is actually a 1.... I also decided not to
call the macro but instead just insert the code right into
the SELECT CASE. So this is what I have...

Sub mcrSaveAs()
'
' Saves workbook as HC&DCReimburse_EE Name_Today's Date
' unless resubmission
'
Dim var1 As Boolean, var2 As Boolean
Select Case var1 & var2
Case 0, 0
ActiveWorkbook.SaveAs ("C:\My
Documents\HC&DCReimburse#" & Sheets("Form").Cells(2,
8).Value & "_" & Sheets("Form").Cells(6, 6).Value & "_" &
Sheets("Data").Cells(2, 5).Value & ".xls")
Case 1, 1
Worksheets("Form").Select
Range("A4:I4").Select
Case 1, 0
Worksheets("Form").Select
Range("A4:I4").Select
Case 0, 1
Worksheets("Form").Select
Range("A4:I4").Select
End Select
End Sub

It still isn't working. How does it know what object (the
chkbx) or cell (the linked cell) to look at for the SELECT
CASE? Could this be why it isn't working? Or is it that
I need to use TrueTrue without the quotations instead of
1, 1?

Any ideas?

Thank you! I am such a novice! I know what is possible
but I just don't know how to get there yet.
Kathryn
-----Original Message-----
Given 2 variables (conditions) you have 4 possible
Click to expand...
outcomes: (0 0),(0 1),(1 0),(1 1) where 0 = FALSE and 1
=TRUE.
The SELECT CASE construction is eminently suitable to
Click to expand...
handle the states of the 2 conditions:
 
B

Bob Phillips

Try my previous reply.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
G

Guest

Bob, Trev and AA2e72E ~

Thank you all for your help. I couldn't get any of them
to work on their own for some reason but with a little
trial and error, I came up with the following which is
working just great.

Sub mcrSaveAs()
'
' mcrSaveAs Macro
' Macro written 12/31/03 by Kathryn T
'
' Saves workbook as HC&DCReimburse_EE Name_Today's Date
' unless resubmission
'
With Worksheets("Data")
If .Range("E6").Value = True Or .Range("E7").Value
= True Then
Worksheets("Form").Select
Range("A4:I4").Select
Else
ActiveWorkbook.SaveAs ("C:\My
Documents\HC&DCReimburse#" & Sheets("Form").Cells(2,
8).Value & "_" & Sheets("Form").Cells(6, 6).Value & "_" &
Sheets("Data").Cells(2, 5).Value & ".xls")
End If
End With

End Sub

Thanks Again!
K
 
K

Kathryn

Bob, Trev and AA2e72E ~

Thank you all for your help. I couldn't get any of them
to work on their own for some reason but with a little
trial and error, I came up with the following which is
working just great.

Sub mcrSaveAs()
'
' mcrSaveAs Macro
' Macro written 12/31/03 by Kathryn T
'
' Saves workbook as HC&DCReimburse_EE Name_Today's Date
' unless resubmission
'
With Worksheets("Data")
If .Range("E6").Value = True Or .Range("E7").Value
= True Then
Worksheets("Form").Select
Range("A4:I4").Select
Else
ActiveWorkbook.SaveAs ("C:\My
Documents\HC&DCReimburse#" & Sheets("Form").Cells(2,
8).Value & "_" & Sheets("Form").Cells(6, 6).Value & "_" &
Sheets("Data").Cells(2, 5).Value & ".xls")
End If
End With

End Sub

Thanks Again!
K
 
Top