How to run a private sub in the workbook from another workbook

[ddiicc]

Hi,

Currently I have 2 workbooks... WB1 is HSA Yield, WB2 is Pivot Yield.
In WB1, I have a command button link to the private sub name HSAYield ().
In WB2, I have a command button link to the private sub name PivotYield()

Now, I am writing codes in WB1 Private Sub HSA Yield as show below :-
The problem that I am facing is that the last line that states : Call
AllPHC..... cannot be activated. Could somebody help me to resolve this.
Thanks.

Private Sub HSAYield_Click()
'
' HSA Yield Macro
' Macro recorded 6/9/2005
'
' Crunching of Pivot Height Check All Models All Lines Yield
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Worksheets("Selections").Select
Range("D3").Copy
Workbooks.Open Filename:="D:\Templates For HSA\PHC Template All Lines
All Models.xls"
Workbooks("PHC Template All Lines All Models.xls").Activate
Worksheets("Selections").Select
ActiveSheet.Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Call AllPHCStationsAllModels1stAndFinalPass_Click

End Sub

 

[Robert Mulroney]

It's not possible to call a private function from outside the namespace of
that function. That's why we label a procedure private, so that it's not
possible for another object to call or even know about that function. If you
want to call a procedure from another workbook then you'll need to make the
procedure public.

ie
'[WB1]
public sub AllPHCStationsAllModels1stAndFinalPass_Click(.....)
.....
end sub

Also make sure that the workbook which contains your procedure
"AllPHCStationsAllModels1stAndFinalPass_Click" is open. It then may or may
not be necessary to properly address the procedure call
ie.

Sub HSAYield_Click()
..
..
..
..

dim strWB1Name as string
strWB1Name="my workbooks name....."
Application.Workbooks(strWB1Name)._
AllPHCStationsAllModels1stAndFinalPass_Click

end sub

 

[DM Unseen]

Although Robert is right about what a private sub means, in XL there is
a hack to accomplish this:

using Application.Run you can specify workbook and macro name, and it
will execute, regardless if private or public, and regardless if it is
executed from the same or another workbook as the called procedure.

example usage: Application.Run "myprivatesub.xls!test"

DM Unseen

 

[wilro85]

I'm having a similar yet different conundrum. On a workbook I'm making
I'm trying to have a customizable interface that will hide items a
desired, however, I get an error calling the program I want to ru
across sheets. Thing that bothers me about this is I know bot
programs work if I run them independently. I also know that program
can run across sheets. Where am I going wrong?

Private Sub CheckBox1_Click()
Application.ScreenUpdating = False
If CheckBox1 = True Then
Range("a18").Select
Selection.EntireRow.Hidden = False
CommandButton7.Visible = True
Call Sheets("Overview").special
Else
If Worksheets("overview").Range("b58").Value = 0 An
Worksheets("overview").Range("c58").Value = 0 Then
Range("a18").Select
Selection.EntireRow.Hidden = True
CommandButton7.Visible = False
Call Sheets("overview").special
Else
Application.ScreenUpdating = True
MsgBox ("The object you have choosen to hide contains values and thu
can't be hidden.")
End If
End If
End Sub

*Other sheet*

Public Sub special()
If Sheets("summary").CheckBox1 = True Then
Range("a61").Select
Selection.EntireRow.Hidden = False
Else
Range("a61").Select
Selection.EntireRow.Hidden = True
End If
End Sub

*Edit - More info*

The specific error I get when I try to run the program is:

Runtime Error "1004"

Select method or Range class failed.

What does that mean