Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retain Workbook Name throughout Module
Hi,
I use the below to capture the name of the file that the macro was run from, this is then used in the Subs code before another Sub is called. My question is how do I retain the Workbook Name so that I can use in another Sub within the same module. iFname = Application.ActiveWorkbook.Name Thanks, Rob |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retain Workbook Name throughout Module
Define iFname in the declarations portion of the code module (above the first
Sub or Function definition). Do not declare it in any Subs or Functions in the code module: Option Explicit Dim iFname As String Sub MySub1() iFname = Application.ActiveWorkbook.Name End Sub Sub MySub2() If iFname="" then MsgBox "You have not run MySub1 yet" Else MsgBox iFname End if End Sub If you need it to be 'visible' across several modules, declare it using Public instead of Dim. "Rob" wrote: Hi, I use the below to capture the name of the file that the macro was run from, this is then used in the Subs code before another Sub is called. My question is how do I retain the Workbook Name so that I can use in another Sub within the same module. iFname = Application.ActiveWorkbook.Name Thanks, Rob . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retain Workbook Name throughout Module
Thank you, couldn't find this in VBA help so much appreciated.
Rob "JLatham" wrote in message ... Define iFname in the declarations portion of the code module (above the first Sub or Function definition). Do not declare it in any Subs or Functions in the code module: Option Explicit Dim iFname As String Sub MySub1() iFname = Application.ActiveWorkbook.Name End Sub Sub MySub2() If iFname="" then MsgBox "You have not run MySub1 yet" Else MsgBox iFname End if End Sub If you need it to be 'visible' across several modules, declare it using Public instead of Dim. "Rob" wrote: Hi, I use the below to capture the name of the file that the macro was run from, this is then used in the Subs code before another Sub is called. My question is how do I retain the Workbook Name so that I can use in another Sub within the same module. iFname = Application.ActiveWorkbook.Name Thanks, Rob . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Store Passwords To Multiple Sheets and To Retain thatPasswords When the Workbook IS closed | Excel Programming | |||
Copy sheet - Break Links to Old Workbook - Retain Formula. | Setting up and Configuration of Excel | |||
Run worksheet module code from workbook module? | Excel Programming | |||
Automatically Delete WorkBook 2 modules by using Workbook 1 module | Excel Programming | |||
After Workbook closes all my combo boxes do not retain dropdown list. | Excel Programming |