Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Cristina Riley   on Nov 02 In MS Office Category.

  
Question Answered By: Mildred Bailey   on Nov 02

Yes, you're quite correct. I was remembering part of the problem only.

A public sub/function in a sheet  module is, indeed, public. However, it's
not directly accessible.

The sheet module is a class module. I.e. it doesn't exist by itself, only
as part of its sheet. So, consider my Sheet1 code:

Option Explicit

Public Sub CommandButton1_Click()
MsgBox "click"
Call UnClick
End Sub

Public Sub UnClick()
MsgBox "unclick"
End Sub

Clearly, clicking the button on sheet 1 will call  the click  subroutine and
that will in turn  call the second subroutine.

My Module1 code:

Option Explicit

Sub a()
Call CommandButton1_Click
Call UnClick
End Sub

refuses to compile and fails if I try to run the sub without compilation.
This is because the code  in Sheet1 doesn't exist in isolation. However, I
can qualify the two subroutines:

Option Explicit

Sub a()
Call Sheet1.CommandButton1_Click
Call Sheet1.UnClick
End Sub

and both get called  fine (and the click sub continues to call the unclick
sub as well). I.e. sub a gives three message  boxes - "click" once, then
"unclick" twice.

There isn't actually anything special about a click subroutine. It's just
that it needs to be named this way so the button can find it - because the
button is not actually bound to the click routine in any way.

All that said, I don't actually recommend calling  methods of a sheet class
from ordinary modules - not quite sure why, it just doesn't "feel" right.

Share: 

 

This Question has 5 more answer(s). View Complete Question Thread

 
Didn't find what you were looking for? Find more on Calling a sub from another sub -- why the error message? Or get search suggestion and latest updates.


Tagged: