Call Subroutine in Excel
 

Please help. I am trying to set up a subroutine that I can call several
times to eliminate having to type this code a bunch of times. The
subroutine that I would like to call is this Newtest. The subroutines where
it is used and Newtest are actually all in the same workbook.

Public Sub Newtest_Click()
Dim NewFillRange, lookuprng As Range
Dim EntityVar, NewRange As String
Dim StartRow, EndRow As Integer

If CheckBoxEntity.Value = False Then Set NewFillRange =
Worksheets("Demo").Range("DemoDept")
If CheckBoxEntity.Value = True Then Set NewFillRange =
Worksheets("Demo").Range("DemoDeptByEntity")

If CheckBoxDept.Value = True Then
ComboBoxDept.Visible = True
If ComboBoxEntity.Value = "<" Then Set NewFillRange =
Worksheets("Demo").Range("DemoDept") 'Show ALL depts

With Worksheets("RFJ").ComboBoxDept
.ListFillRange = NewFillRange.Address(external:=True)
End With


Application.Goto Reference:="R12C14"
Application.Goto Reference:="R5C4"

' All Departments Selected
ElseIf CheckBoxDept.Value = False Then
ComboBoxDept.Visible = False
Application.Goto Reference:="R12C14"
ActiveCell.FormulaR1C1 = "<"
Application.Goto Reference:="R5C4"
End If
End Sub

Where do you put a Public subroutine? It it in the "This Workbook"? How do
you call it in a subroutine? I tried application.Run "Workbookname!Newtest",
but got an error regarding a macro.

Call Subroutine in Excel
 

Put them in a module.

In the VB window, right click your project and insert module. These two
macros show you how to call a piece of code, as the second calls the first:
Sub test()

MsgBox "Hello"

End Sub

Sub test2()

Call test

End Sub


"DogLover" wrote:

Please help. I am trying to set up a subroutine that I can call several
times to eliminate having to type this code a bunch of times. The
subroutine that I would like to call is this Newtest. The subroutines where
it is used and Newtest are actually all in the same workbook.

Public Sub Newtest_Click()
Dim NewFillRange, lookuprng As Range
Dim EntityVar, NewRange As String
Dim StartRow, EndRow As Integer

If CheckBoxEntity.Value = False Then Set NewFillRange =
Worksheets("Demo").Range("DemoDept")
If CheckBoxEntity.Value = True Then Set NewFillRange =
Worksheets("Demo").Range("DemoDeptByEntity")

If CheckBoxDept.Value = True Then
ComboBoxDept.Visible = True
If ComboBoxEntity.Value = "<" Then Set NewFillRange =
Worksheets("Demo").Range("DemoDept") 'Show ALL depts

With Worksheets("RFJ").ComboBoxDept
.ListFillRange = NewFillRange.Address(external:=True)
End With


Application.Goto Reference:="R12C14"
Application.Goto Reference:="R5C4"

' All Departments Selected
ElseIf CheckBoxDept.Value = False Then
ComboBoxDept.Visible = False
Application.Goto Reference:="R12C14"
ActiveCell.FormulaR1C1 = "<"
Application.Goto Reference:="R5C4"
End If
End Sub

Where do you put a Public subroutine? It it in the "This Workbook"? How do
you call it in a subroutine? I tried application.Run "Workbookname!Newtest",
but got an error regarding a macro.

Call Subroutine in Excel
 

I see how that part works. However, I get an error when my module to the code

If CheckBoxEntity.Value = False Then Set NewFillRange =
Worksheets("Demo").Range("DemoDept")

I think I may need to pass CheckBoxEntity.Value to the subroutine from the
call statement, but I'm not sure.


"Sam Wilson" wrote:

Put them in a module.

In the VB window, right click your project and insert module. These two
macros show you how to call a piece of code, as the second calls the first:
Sub test()

MsgBox "Hello"

End Sub

Sub test2()

Call test

End Sub


"DogLover" wrote:

Please help. I am trying to set up a subroutine that I can call several
times to eliminate having to type this code a bunch of times. The
subroutine that I would like to call is this Newtest. The subroutines where
it is used and Newtest are actually all in the same workbook.

Public Sub Newtest_Click()
Dim NewFillRange, lookuprng As Range
Dim EntityVar, NewRange As String
Dim StartRow, EndRow As Integer

If CheckBoxEntity.Value = False Then Set NewFillRange =
Worksheets("Demo").Range("DemoDept")
If CheckBoxEntity.Value = True Then Set NewFillRange =
Worksheets("Demo").Range("DemoDeptByEntity")

If CheckBoxDept.Value = True Then
ComboBoxDept.Visible = True
If ComboBoxEntity.Value = "<" Then Set NewFillRange =
Worksheets("Demo").Range("DemoDept") 'Show ALL depts

With Worksheets("RFJ").ComboBoxDept
.ListFillRange = NewFillRange.Address(external:=True)
End With


Application.Goto Reference:="R12C14"
Application.Goto Reference:="R5C4"

' All Departments Selected
ElseIf CheckBoxDept.Value = False Then
ComboBoxDept.Visible = False
Application.Goto Reference:="R12C14"
ActiveCell.FormulaR1C1 = "<"
Application.Goto Reference:="R5C4"
End If
End Sub

Where do you put a Public subroutine? It it in the "This Workbook"? How do
you call it in a subroutine? I tried application.Run "Workbookname!Newtest",
but got an error regarding a macro.

Call Subroutine in Excel
 

You can pass variables as follows:

Sub test(byval strDemo as string)

MsgBox "Hello " & strDemo

End Sub

Sub test2()

dim myStr as string
myStr = "Sam"

Call test(myStr)

End Sub


"DogLover" wrote:

I see how that part works. However, I get an error when my module to the code

If CheckBoxEntity.Value = False Then Set NewFillRange =
Worksheets("Demo").Range("DemoDept")

I think I may need to pass CheckBoxEntity.Value to the subroutine from the
call statement, but I'm not sure.


"Sam Wilson" wrote:

Put them in a module.

In the VB window, right click your project and insert module. These two
macros show you how to call a piece of code, as the second calls the first:
Sub test()

MsgBox "Hello"

End Sub

Sub test2()

Call test

End Sub


"DogLover" wrote:

Please help. I am trying to set up a subroutine that I can call several
times to eliminate having to type this code a bunch of times. The
subroutine that I would like to call is this Newtest. The subroutines where
it is used and Newtest are actually all in the same workbook.

Public Sub Newtest_Click()
Dim NewFillRange, lookuprng As Range
Dim EntityVar, NewRange As String
Dim StartRow, EndRow As Integer

If CheckBoxEntity.Value = False Then Set NewFillRange =
Worksheets("Demo").Range("DemoDept")
If CheckBoxEntity.Value = True Then Set NewFillRange =
Worksheets("Demo").Range("DemoDeptByEntity")

If CheckBoxDept.Value = True Then
ComboBoxDept.Visible = True
If ComboBoxEntity.Value = "<" Then Set NewFillRange =
Worksheets("Demo").Range("DemoDept") 'Show ALL depts

With Worksheets("RFJ").ComboBoxDept
.ListFillRange = NewFillRange.Address(external:=True)
End With


Application.Goto Reference:="R12C14"
Application.Goto Reference:="R5C4"

' All Departments Selected
ElseIf CheckBoxDept.Value = False Then
ComboBoxDept.Visible = False
Application.Goto Reference:="R12C14"
ActiveCell.FormulaR1C1 = "<"
Application.Goto Reference:="R5C4"
End If
End Sub

Where do you put a Public subroutine? It it in the "This Workbook"? How do
you call it in a subroutine? I tried application.Run "Workbookname!Newtest",
but got an error regarding a macro.