Combine macros mismatch
 

I try combining the 2 macros below into 1 in the same sheet, the codes shown.
But I was prompted error below---
Run-time error '13':Type mismatch

Sub CallMacros()
Call ButtonReset_Click
Call Worksheet_Calculate
End Sub


Private Sub Worksheet_Calculate()
Dim r As Range
Set r = Range("E65")
If r = "pop" Then Msg = MsgBox("Joint capacity is insufficient.Re-select a
bigger section size.", vbExclamation + vbOKOnly, "Rectangular Hollow Section")

End Sub

Sub ButtonReset_Click()
If MsgBox("Are you sure you want to permanently delete the data?", _
vbQuestion + vbYesNo + vbDefaultButton, "Rectangular Hollow Section") = vbNo
Then Exit Sub
For Each cell In ActiveSheet.UsedRange
If cell.Interior.ColorIndex = 20 Then
cell.Formula = ""
End If
Next cell
End Sub


Appreciate any help on where I went wrong. Thanks.

Combine macros mismatch
 

You have not said on what command you are getting the error message
Assuming error in in the CallMacros macro try using the following
commands
Change book & shhet names to suit


Code:
--------------------

Application.Run "Book1.xls!Sheet1.ButtonReset_Click"
Application.Run "Book1.xls!Sheet1.Worksheet_Calculate"

--------------------


--
mudraker

If my reply has assisted or failed to assist you I welcome your
Feedback.

www.thecodecage.com
------------------------------------------------------------------------
mudraker's Profile: http://www.thecodecage.com/forumz/member.php?userid=18
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=64570

Combine macros mismatch
 

I think I would do it like this. If I wanted the worksheet_calculate to
checkr then just call it from there.

Sub combineem()
Call ButtonReset_Click
Call checkr
End Sub

Sub ButtonReset_Click()
If MsgBox("Are you sure you want to permanently delete the data?", _
vbQuestion + vbYesNo + vbDefaultButton, "Rectangular Hollow Section") _
= vbNo Then Exit Sub

For Each c In ActiveSheet.UsedRange
If c.Interior.ColorIndex = 20 Then
c.ClearContents
End If
Next c
End Sub

Sub checkr()
Dim r As Range
Set r = Range("b1")
If lcase(r) = "pop" Then _
MsgBox "Joint capacity is insufficient.Re-select a bigger section size.", _
vbExclamation + vbOKOnly, "Rectangular Hollow Section"
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"amelia" wrote in message
...
I try combining the 2 macros below into 1 in the same sheet, the codes
shown.
But I was prompted error below---
Run-time error '13':Type mismatch

Sub CallMacros()
Call ButtonReset_Click
Call Worksheet_Calculate
End Sub


Private Sub Worksheet_Calculate()
Dim r As Range
Set r = Range("E65")
If r = "pop" Then Msg = MsgBox("Joint capacity is insufficient.Re-select a
bigger section size.", vbExclamation + vbOKOnly, "Rectangular Hollow
Section")

End Sub

Sub ButtonReset_Click()
If MsgBox("Are you sure you want to permanently delete the data?", _
vbQuestion + vbYesNo + vbDefaultButton, "Rectangular Hollow Section") =
vbNo
Then Exit Sub
For Each cell In ActiveSheet.UsedRange
If cell.Interior.ColorIndex = 20 Then
cell.Formula = ""
End If
Next cell
End Sub


Appreciate any help on where I went wrong. Thanks.

Combine macros mismatch
 

Ive tried like you suggested--Sub checkr().
But the message box doesn't appear when cell E65="pop" and was prompted a
mismatch error. Cell E65 is dependent on some other cells that caused E65 to
change to "pop".

My previous code..
I was also prompted a mismatch error and this is highlighted--If r = "pop"
Then.

Appreciate any help..

"Don Guillett" wrote:

I think I would do it like this. If I wanted the worksheet_calculate to
checkr then just call it from there.

Sub combineem()
Call ButtonReset_Click
Call checkr
End Sub

Sub ButtonReset_Click()
If MsgBox("Are you sure you want to permanently delete the data?", _
vbQuestion + vbYesNo + vbDefaultButton, "Rectangular Hollow Section") _
= vbNo Then Exit Sub

For Each c In ActiveSheet.UsedRange
If c.Interior.ColorIndex = 20 Then
c.ClearContents
End If
Next c
End Sub

Sub checkr()
Dim r As Range
Set r = Range("b1")
If lcase(r) = "pop" Then _
MsgBox "Joint capacity is insufficient.Re-select a bigger section size.", _
vbExclamation + vbOKOnly, "Rectangular Hollow Section"
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"amelia" wrote in message
...
I try combining the 2 macros below into 1 in the same sheet, the codes
shown.
But I was prompted error below---
Run-time error '13':Type mismatch

Sub CallMacros()
Call ButtonReset_Click
Call Worksheet_Calculate
End Sub


Private Sub Worksheet_Calculate()
Dim r As Range
Set r = Range("E65")
If r = "pop" Then Msg = MsgBox("Joint capacity is insufficient.Re-select a
bigger section size.", vbExclamation + vbOKOnly, "Rectangular Hollow
Section")

End Sub

Sub ButtonReset_Click()
If MsgBox("Are you sure you want to permanently delete the data?", _
vbQuestion + vbYesNo + vbDefaultButton, "Rectangular Hollow Section") =
vbNo
Then Exit Sub
For Each cell In ActiveSheet.UsedRange
If cell.Interior.ColorIndex = 20 Then
cell.Formula = ""
End If
Next cell
End Sub


Appreciate any help on where I went wrong. Thanks.