Calling a sub on another worksheet
 

Hi Folks,

I am trying to use a checkbox to show or hide an additional worksheet. That
part is easy - but what I am also trying to do is call a subroutine (Private
Sub ResetForm_Click() ) on the other worksheet (Sheet7) that resets that
sheet to a particular base state (i.e. removes all images from image boxes,
clears cell contents, moves shapes, etc.) I'm not sure if it is possible to
do, and I only want to delete the info on that specific sheet. I could just
copy and paste the contents of ResetForm_Click into the code for the checkbox
change event, but I'm sure there has to be a better way to call a private
function. Or do I have to make it a public function?

Here's what I have for code so far:

Private Sub CheckBox1_Change()

Dim RemSection As Long

If Sheet3.CheckBox1.Value = True Then
Sheet7.Visible = True
Else
RemSection = MsgBox("Are you sure? Unchecking this box removes all
info from the additional section. This cannot be undone!", vbYesNo)
If RemSection = vbYes Then
Sheet7.Visible = False
Run Sheet7.ResetForm 'This line does not seem to work!
ElseIf RemSection = vbNo Then
Sheet3.CheckBox1.Value = True
End If
End If

End Sub

Any suggestions are most appreciated.

Thanks!

Scott

Calling a sub on another worksheet
 

The ResetForm is a commandbutton from the Control toolbox toolbar, right?

If yes, then you have a few choices:

Option Explicit
Sub testme01()
Sheet7.ResetForm.Value = True
'or
Worksheets("SheetNameHere").ResetForm.Value = True

'or if you've removed the "Private" from the _click event procedu
'Sub ResetForm_Click(), not Private Sub ResetForm_Click
Call Sheet7.ResetForm_Click

'or
Application.Run "'" & ThisWorkbook.Name & "'!sheet7.ResetForm_Click"
End Sub



mooresk257 wrote:

Hi Folks,

I am trying to use a checkbox to show or hide an additional worksheet. That
part is easy - but what I am also trying to do is call a subroutine (Private
Sub ResetForm_Click() ) on the other worksheet (Sheet7) that resets that
sheet to a particular base state (i.e. removes all images from image boxes,
clears cell contents, moves shapes, etc.) I'm not sure if it is possible to
do, and I only want to delete the info on that specific sheet. I could just
copy and paste the contents of ResetForm_Click into the code for the checkbox
change event, but I'm sure there has to be a better way to call a private
function. Or do I have to make it a public function?

Here's what I have for code so far:

Private Sub CheckBox1_Change()

Dim RemSection As Long

If Sheet3.CheckBox1.Value = True Then
Sheet7.Visible = True
Else
RemSection = MsgBox("Are you sure? Unchecking this box removes all
info from the additional section. This cannot be undone!", vbYesNo)
If RemSection = vbYes Then
Sheet7.Visible = False
Run Sheet7.ResetForm 'This line does not seem to work!
ElseIf RemSection = vbNo Then
Sheet3.CheckBox1.Value = True
End If
End If

End Sub

Any suggestions are most appreciated.

Thanks!

Scott

--

Dave Peterson

Calling a sub on another worksheet
 

Thanks Dave - setting the command button did the trick. This was the final
version of the code:

Private Sub CheckBox1_Change()

Dim RemSection As Long

If Sheet3.CheckBox1.Value = True Then
Sheet7.Visible = True
Else
RemSection = MsgBox("Unchecking this box removes all info from the
additional section.", vbYesNo)
If RemSection = vbYes Then
Sheet7.ResetForm.Value = True
Sheet7.Visible = False
ElseIf RemSection = vbNo Then
Sheet3.CheckBox1.Value = True
End If
End If

End Sub

I found that I had to move the "Sheet7.Visible = False" line after the sub
call line, otherwise I would get an error.

Thanks again!

"Dave Peterson" wrote:

The ResetForm is a commandbutton from the Control toolbox toolbar, right?

If yes, then you have a few choices:

Option Explicit
Sub testme01()
Sheet7.ResetForm.Value = True
'or
Worksheets("SheetNameHere").ResetForm.Value = True

'or if you've removed the "Private" from the _click event procedu
'Sub ResetForm_Click(), not Private Sub ResetForm_Click
Call Sheet7.ResetForm_Click

'or
Application.Run "'" & ThisWorkbook.Name & "'!sheet7.ResetForm_Click"
End Sub



mooresk257 wrote:

Hi Folks,

I am trying to use a checkbox to show or hide an additional worksheet. That
part is easy - but what I am also trying to do is call a subroutine (Private
Sub ResetForm_Click() ) on the other worksheet (Sheet7) that resets that
sheet to a particular base state (i.e. removes all images from image boxes,
clears cell contents, moves shapes, etc.) I'm not sure if it is possible to
do, and I only want to delete the info on that specific sheet. I could just
copy and paste the contents of ResetForm_Click into the code for the checkbox
change event, but I'm sure there has to be a better way to call a private
function. Or do I have to make it a public function?

Here's what I have for code so far:

Private Sub CheckBox1_Change()

Dim RemSection As Long

If Sheet3.CheckBox1.Value = True Then
Sheet7.Visible = True
Else
RemSection = MsgBox("Are you sure? Unchecking this box removes all
info from the additional section. This cannot be undone!", vbYesNo)
If RemSection = vbYes Then
Sheet7.Visible = False
Run Sheet7.ResetForm 'This line does not seem to work!
ElseIf RemSection = vbNo Then
Sheet3.CheckBox1.Value = True
End If
End If

End Sub

Any suggestions are most appreciated.

Thanks!

Scott

--

Dave Peterson
.

Calling a sub on another worksheet
 

Thanks for the suggestion. This works, but -

There is a "Method 'Run' of Object '_Global' failed" error following
subroutine execution.

Trying to sort that out now...

"OssieMac" wrote:

Hi Scott,

Try the following with double quotes and also note full name of sub
inclucing _Click.

Run "Sheet7.ResetForm_Click"


--
Regards,

OssieMac


"mooresk257" wrote:

Hi Folks,

I am trying to use a checkbox to show or hide an additional worksheet. That
part is easy - but what I am also trying to do is call a subroutine (Private
Sub ResetForm_Click() ) on the other worksheet (Sheet7) that resets that
sheet to a particular base state (i.e. removes all images from image boxes,
clears cell contents, moves shapes, etc.) I'm not sure if it is possible to
do, and I only want to delete the info on that specific sheet. I could just
copy and paste the contents of ResetForm_Click into the code for the checkbox
change event, but I'm sure there has to be a better way to call a private
function. Or do I have to make it a public function?

Here's what I have for code so far:

Private Sub CheckBox1_Change()

Dim RemSection As Long

If Sheet3.CheckBox1.Value = True Then
Sheet7.Visible = True
Else
RemSection = MsgBox("Are you sure? Unchecking this box removes all
info from the additional section. This cannot be undone!", vbYesNo)
If RemSection = vbYes Then
Sheet7.Visible = False
Run Sheet7.ResetForm 'This line does not seem to work!
ElseIf RemSection = vbNo Then
Sheet3.CheckBox1.Value = True
End If
End If

End Sub

Any suggestions are most appreciated.

Thanks!

Scott

Calling a sub on another worksheet
 

Which suggestion did you try?

mooresk257 wrote:

Thanks for the suggestion. This works, but -

There is a "Method 'Run' of Object '_Global' failed" error following
subroutine execution.

Trying to sort that out now...

"OssieMac" wrote:

Hi Scott,

Try the following with double quotes and also note full name of sub
inclucing _Click.

Run "Sheet7.ResetForm_Click"


--
Regards,

OssieMac


"mooresk257" wrote:

Hi Folks,

I am trying to use a checkbox to show or hide an additional worksheet. That
part is easy - but what I am also trying to do is call a subroutine (Private
Sub ResetForm_Click() ) on the other worksheet (Sheet7) that resets that
sheet to a particular base state (i.e. removes all images from image boxes,
clears cell contents, moves shapes, etc.) I'm not sure if it is possible to
do, and I only want to delete the info on that specific sheet. I could just
copy and paste the contents of ResetForm_Click into the code for the checkbox
change event, but I'm sure there has to be a better way to call a private
function. Or do I have to make it a public function?

Here's what I have for code so far:

Private Sub CheckBox1_Change()

Dim RemSection As Long

If Sheet3.CheckBox1.Value = True Then
Sheet7.Visible = True
Else
RemSection = MsgBox("Are you sure? Unchecking this box removes all
info from the additional section. This cannot be undone!", vbYesNo)
If RemSection = vbYes Then
Sheet7.Visible = False
Run Sheet7.ResetForm 'This line does not seem to work!
ElseIf RemSection = vbNo Then
Sheet3.CheckBox1.Value = True
End If
End If

End Sub

Any suggestions are most appreciated.

Thanks!

Scott

--

Dave Peterson

Calling a sub on another worksheet
 

Hi Scott,

Try the following with double quotes and also note full name of sub
inclucing _Click.

Run "Sheet7.ResetForm_Click"


--
Regards,

OssieMac


"mooresk257" wrote:

Hi Folks,

I am trying to use a checkbox to show or hide an additional worksheet. That
part is easy - but what I am also trying to do is call a subroutine (Private
Sub ResetForm_Click() ) on the other worksheet (Sheet7) that resets that
sheet to a particular base state (i.e. removes all images from image boxes,
clears cell contents, moves shapes, etc.) I'm not sure if it is possible to
do, and I only want to delete the info on that specific sheet. I could just
copy and paste the contents of ResetForm_Click into the code for the checkbox
change event, but I'm sure there has to be a better way to call a private
function. Or do I have to make it a public function?

Here's what I have for code so far:

Private Sub CheckBox1_Change()

Dim RemSection As Long

If Sheet3.CheckBox1.Value = True Then
Sheet7.Visible = True
Else
RemSection = MsgBox("Are you sure? Unchecking this box removes all
info from the additional section. This cannot be undone!", vbYesNo)
If RemSection = vbYes Then
Sheet7.Visible = False
Run Sheet7.ResetForm 'This line does not seem to work!
ElseIf RemSection = vbNo Then
Sheet3.CheckBox1.Value = True
End If
End If

End Sub

Any suggestions are most appreciated.

Thanks!

Scott