Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calling a worksheet function from another worksheet in same workbo | Excel Programming | |||
Calling a Sub in a Worksheet | Excel Programming | |||
Calling another worksheet | Excel Programming | |||
vb.net calling worksheet by name | Excel Programming | |||
Calling a different worksheet??? | Excel Programming |