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