Combine macros mismatch

A

amelia

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.
 
M

mudraker

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


Code
-------------------

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

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

--
mudrake

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

www.thecodecage.co
 
D

Don Guillett

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
 
A

amelia

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..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top