Calling a Worksheet Level Procedure from a UserForm Button Procedu

[RyanH]

I have a CheckBox (chkShowTotal) in a cell on Sheets("QUOTE"). The code for
chkShowTotal is located in the Worksheet Module. The check box hides or
displays the Sub Total and TOTAL using comments. I have a Userform with a
button on it that executes a procedure and at the end of that procedure I
want to call the chkShowTotal_Click Procedure, but I am getting an error:
"Compile Error: Sub or Function not defined.", Why? Here is my code:

Private Sub cmbAddAsNew_Click()

' my userform code here
Call chkShowTotal_Click

End Sub


Public Sub chkShowTotal_Click()

Dim myTotal As Range


' applies the Sub Total on the QUOTE sheet
Set myTotal = ActiveSheet.Columns("G:G").Find(What:="TOTAL", _
After:=Cells(10, 7), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

ActiveSheet.Unprotect "AdTech"

' shows or hides Total and Sub Total, by using comments as storage
With myTotal
If chkShowTotal = True Then
.Offset(-3, 1).Formula = .Offset(-3, 1).Comment.Text
.Offset(-3, 1).Comment.Delete
.Offset(0, 1).Formula = .Offset(0, 1).Comment.Text
.Offset(0, 1).Comment.Delete
Else
.Offset(-3, 1).AddComment CStr(.Offset(-3, 1).Formula)
.Offset(-3, 1).ClearContents
.Offset(0, 1).AddComment CStr(.Offset(0, 1).Formula)
.Offset(0, 1).ClearContents
End If
End With

ActiveSheet.Protect "AdTech"

End Sub

 

[Peter T]

You could do it like this

' userform code
Dim oSht As Object
Set oSht = Worksheets("Sheet1") ' "sheet name containing code"
Call oSht.chkShowTotal_Click
or if safe to hardcode the sheet's Codename

Call Sheet1.chkShowTotal_Click

Much better though would be to move the majority of the sheet code into a
normal module, renamed something like this

Sub ShowTotal(ws as Worksheet)
and in this proc replace ActiveSheet with ws

in the click event in your sheet
Call ShowTotal(Me)

and from your userform like this
Call ShowTotal(ActiveSheet) ' or whatever sheet object you want to process

Regards,
Peter T

 

[RyanH]

Just out of curiosity, how is it much better. Does it run faster?
--
Cheers,
Ryan

You could do it like this

' userform code
Dim oSht As Object
Set oSht = Worksheets("Sheet1") ' "sheet name containing code"
Call oSht.chkShowTotal_Click
or if safe to hardcode the sheet's Codename

Call Sheet1.chkShowTotal_Click

Much better though would be to move the majority of the sheet code into a
normal module, renamed something like this

Sub ShowTotal(ws as Worksheet)
and in this proc replace ActiveSheet with ws

in the click event in your sheet
Call ShowTotal(Me)

and from your userform like this
Call ShowTotal(ActiveSheet) ' or whatever sheet object you want to process

Regards,
Peter T

 

[RyanH]

I also tried to hardcode it and it doesn't seem to work. I am now getting an
error that says " Object does not support this property or method" The code
for the check box is in the Worksheet Module.
--
Cheers,
Ryan

You could do it like this

' userform code
Dim oSht As Object
Set oSht = Worksheets("Sheet1") ' "sheet name containing code"
Call oSht.chkShowTotal_Click
or if safe to hardcode the sheet's Codename

Call Sheet1.chkShowTotal_Click

Much better though would be to move the majority of the sheet code into a
normal module, renamed something like this

Sub ShowTotal(ws as Worksheet)
and in this proc replace ActiveSheet with ws

in the click event in your sheet
Call ShowTotal(Me)

and from your userform like this
Call ShowTotal(ActiveSheet) ' or whatever sheet object you want to process

Regards,
Peter T

 

[Peter T]

First, I take it both suggestions worked for you.

Generally only code that's specific to the particular sheet should go in its
module. Otherwise there are several reasons it's better in an ordinary
module (or a class module).

Most obvious of course is the sheet might be deleted. Other reasons,
wherever possible avoid hardcoding the name. If not using "CodeName" and
using the "As Object" example I gave, you'll convert to "Late Binding". In
theory that's a tad slower, which you may not notice, but you'll lose the
intellisense.

For ease of coding the procedure is more re-useable in an ordinary module.

A bit more obscure, any corruption behind the scenes is more difficult to
remove from object modules than ordinary modules, using say Rob Bovey's code
cleaner. This alone is one reason to avoid re-developing, in particular
debugging and "stopping" code, in object modules when there is no good
reason to put the code there in the first place.

I'm sure there are loads of other reasons but I guess that's enough!

Regards,
Peter T

Just out of curiosity, how is it much better. Does it run faster?

 

[Peter T]

Have you put in some new test code and forgotten to change Private to Public
(as you had done in your original). Without seeing your code hard it's hard
to say without more without throwing more long shot guesses at you.

Regards,
Peter T

I also tried to hardcode it and it doesn't seem to work. I am now getting
an
error that says " Object does not support this property or method" The
code
for the check box is in the Worksheet Module.

 

[RyanH]

I appreciate the follow up. My current code is below. I am getting an error
indicated below: "Object does not support property or method."


My UserForm Control Button Code:

Private Sub cmbAddAsNew_Click()

' I have code here to manipulate some cells and rows

ERROR=> Call Worksheet("QUOTE").chkShowTotal_Click

End Sub


My CheckBox Code which is located in the Sheets("QUOTE") Module:
Note: The checkbox is located on the worksheet "QUOTE"

Public Sub chkShowTotal_Click()

Dim mySubTotal As Range

' applies the Sub Total on the QUOTE sheet
Set mySubTotal = Sheets("QUOTE").Columns("G:G").Find(What:="Sub Total", _
After:=Cells(10, 7), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

ActiveSheet.Unprotect "AdTech"

' shows or hides Total and Sub Total, by using comments as storage
' an error occurs when the formula = no comment avialable
With mySubTotal
.Offset(0, 1).Formula = "=IF(ISERROR(SUM(H10:H" & mySubTotal.Row - 1
& ")),0,SUM(H10:H" & mySubTotal.Row - 1 & "))"
.Offset(0, 1).ClearComments
.Offset(0, 1).AddComment = CStr(.Offset(0, 1).Formula)
On Error Resume Next
If chkShowTotal = True Then
.Offset(0, 1).Formula = .Offset(0, 1).Comment.Text ' Sub
Total
.Offset(0, 1).ClearComments
.Offset(3, 1).Formula = .Offset(3, 1).Comment.Text ' TOTAL
.Offset(3, 1).ClearComments
Else
.Offset(0, 1).AddComment CStr(.Offset(0, 1).Formula)
.Offset(0, 1).ClearContents
.Offset(3, 1).AddComment CStr(.Offset(3, 1).Formula)
.Offset(3, 1).ClearContents
End If
On Error GoTo 0
End With

ActiveSheet.Protect "AdTech"

End Sub

 

[Peter T]

try changing

ERROR=> Call Worksheet("QUOTE").chkShowTotal_Click

to > Call WorksheetS("QUOTE").chkShowTotal_Click

If it still doesn't work, add a simple click event to an already added
control, eg a commandbutton, and call it in your form

' in Sheet1
Public Sub CommandButton1_Click() ' ensure it's Public
MsgBox "Hello from " & Me.Name
End Sub

' from anywhere outside the sheet module, eg the form's click event

Private Sub UserForm_Click()
Dim oSht As Object
Set oSht = Worksheets("Sheet1")
oSht.CommandButton1_Click

End Sub

Regards,
Peter T

PS, whilst it might be interesting to get it working, why not look at
putting the code in an ordinary module as I suggested