B
Brettjg
This post replaces the one I just marked as no longer required.
I have two problems:
1. I have a workbook called "1. TOOLS.xls" that is almost always open (I
can't do much without it). I want to set up VB with either a Public variable
or a Public Const so that I can just refer to this book as TLS and to it's
two main worksheets by T_TL and T_CONT. I know that I can use
Public TLS as Workbook
and then set it when I open 1. TOOLS with
Set TLS = Workbooks("1. TOOLS.xls")
and then set the sheets, but the problem is that if I have a debug instance
and have to reset then I lose the value of the Public variables. I don't
think there is a way of getting them back without manually resetting them
again, which just won't do.
Even if I had
Public Const TLS as String = "1. TOOLS.xls"
I still have the problem os setting it, and as far as I can see I can't have
Public Const TLS as Workbook = Workbooks("1. TOOLS.xls")
SO THE QUESTION IS HOW DO I DO IT PLEASE?
The second problem follows on from that. I have a workbook "1. FINANCE
surname.xls" where surname varies. This book is the centre of the system that
runs of it (about ten workbooks).
I want to be able to do the same thing with this workbook as described
above for TOOLS, the difference being that FINANCE has the variable surname
in it. I currently have a macro the does all the setting for the sheets when
ant FINANCE is opened, but again if there's a crash then the Public variables
lose their values. If I have to set them every time I run one of scores of
macros then it defeats the purpose of having a Public variable (which is
supposed to making life easier!).
Thanks for any help that can be supplied. Regards, Brett
I have two problems:
1. I have a workbook called "1. TOOLS.xls" that is almost always open (I
can't do much without it). I want to set up VB with either a Public variable
or a Public Const so that I can just refer to this book as TLS and to it's
two main worksheets by T_TL and T_CONT. I know that I can use
Public TLS as Workbook
and then set it when I open 1. TOOLS with
Set TLS = Workbooks("1. TOOLS.xls")
and then set the sheets, but the problem is that if I have a debug instance
and have to reset then I lose the value of the Public variables. I don't
think there is a way of getting them back without manually resetting them
again, which just won't do.
Even if I had
Public Const TLS as String = "1. TOOLS.xls"
I still have the problem os setting it, and as far as I can see I can't have
Public Const TLS as Workbook = Workbooks("1. TOOLS.xls")
SO THE QUESTION IS HOW DO I DO IT PLEASE?
The second problem follows on from that. I have a workbook "1. FINANCE
surname.xls" where surname varies. This book is the centre of the system that
runs of it (about ten workbooks).
I want to be able to do the same thing with this workbook as described
above for TOOLS, the difference being that FINANCE has the variable surname
in it. I currently have a macro the does all the setting for the sheets when
ant FINANCE is opened, but again if there's a crash then the Public variables
lose their values. If I have to set them every time I run one of scores of
macros then it defeats the purpose of having a Public variable (which is
supposed to making life easier!).
Thanks for any help that can be supplied. Regards, Brett