Use of Public for data declaration in Excel 2000
Typically public variable should be put in standard modules (Insert, Module)
rather than sheet modules. They can be seen from anywhere in the project.
Alternatively you can pint to them by using the codename of the sheet
module:
Sub TestingSh1()
MsgBox Sheet1.InUseCol, vbOKOnly, "Box 2"
End Sub
--
Jim
"Gandalph" wrote in message
...
|I want to use the same data in sub routines in different sheets of a
workbook.
| I wrote the following test routines, the first pair "in Sheet 1" and the
| last "in Sheet 2"
| The variable InUseCol was accessible to both routines in Sheet 1, but the
| routine in Sheet 2 could not access it - returns a null value
|
| Sheet 1
|
| Public InUseCol As Integer
| Sub GenTest()
|
| InUseCol = 5
| MsgBox InUseCol, vbOKOnly, "Selected Column"
| End Sub
| Sub TestingSh1()
| MsgBox InUseCol, vbOKOnly, "Box 2"
| End Sub
|
| Sheet 2
|
| Sub TestingSh2()
| MsgBox InUseCol, vbOKOnly, "Box 3"
| End Sub
|
| Assistance much appreciated
| Gandalph
|