ExcelBanter - Calling a macro from an If statement

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Calling a macro from an If statement (https://www.excelbanter.com/excel-programming/294150-calling-macro-if-statement.html)

kathryn

Calling a macro from an If statement

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!

Trev[_3_]

Calling a macro from an If statement

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
-----Original Message-----
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!
.

Bob Phillips[_6_]

Calling a macro from an If statement

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)

"Kathryn" wrote in message
...
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!

kathryn

Calling a macro from an If statement

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
outcomes: (0 0),(0 1),(1 0),(1 1) where 0 = FALSE and 1
=TRUE.

The SELECT CASE construction is eminently suitable to
handle the states of the 2 conditions:

Sub aa()
Dim var1 As Boolean, var2 As Boolean
Select Case var1 & var2
Case "TrueTrue"
' your action
Case "TrueFalse"
'your action
Case "FalseTrue"
'your action
Case "FalseFalse"
'your action
End Select
End Sub
.

Bob Phillips[_6_]

Calling a macro from an If statement

Try my previous reply.

--

HTH

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

"Kathryn" wrote in message
...
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
outcomes: (0 0),(0 1),(1 0),(1 1) where 0 = FALSE and 1
=TRUE.

The SELECT CASE construction is eminently suitable to
handle the states of the 2 conditions:

Sub aa()
Dim var1 As Boolean, var2 As Boolean
Select Case var1 & var2
Case "TrueTrue"
' your action
Case "TrueFalse"
'your action
Case "FalseTrue"
'your action
Case "FalseFalse"
'your action
End Select
End Sub
.

No Name

Calling a macro from an If statement

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

-----Original Message-----
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!
.

kathryn

Calling a macro from an If statement

All times are GMT +1. The time now is 05:41 PM.