Prev: Programmable Column Sequencing?
Next: Advanced compare of two worksheets
From: mooresk257 on 25 May 2010 18:08
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
From: Dave Peterson on 25 May 2010 18:21
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 procedure:
'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
From: mooresk257 on 27 May 2010 16:39
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 procedure:
> '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
> .
>
From: mooresk257 on 27 May 2010 17:03
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
From: Dave Peterson on 27 May 2010 17:08
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
 |  Next  |  Last
Pages: 1 2
Prev: Programmable Column Sequencing?
Next: Advanced compare of two worksheets