Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Call a subroutine using variable subroutine name | Excel Discussion (Misc queries) | |||
call Excel subroutine from Sql Server? | Excel Programming | |||
Can't call a subroutine | Excel Programming | |||
Call subroutine from another workbook | Excel Programming | |||
Call Subx for excel 97 Add-In subroutine | Excel Programming |