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.

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

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

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