0

I have a workbook that is programmed upon opening to open a new copy of a second workbook and save the value of the second book's name in a variable that can be accessed by a private sub routine for an ActiveX control.

In a separate module I have the value:

'variable holding the name of the new workbook
Public sarname As String

In the 'ThisWorkbook' object I have the code:

Private Sub Workbook_Open()

'creates a new workbook and saves name in public variable
Dim SARWorkbook As Workbook
Set SARWorkbook = Workbooks.Add("C:\Users\...xlsm")
Module1.sarname = SARWorkbk.Name

End Sub

Finally, I am using this value in the code:

Private Sub FilBox1_Click()

'If box is checked, a "Yes" string is entered into range object of new workbook with name saved in Module1.sarname
If FilBox1.Value = True Then
     Workbooks(Module1.sarname).Worksheets("COC Form").Range("b46").Value = "Yes"
Else
     Workbooks(Module1.sarname).Worksheets("COC Form").Range("b46").Value = "No"
End If

End Sub

The ActiveX control after responding to its status, is supposed place a "yes" or "no" answer into a cell in the new workbook using the name stored in "sarname". After doing some investigating with MsgBox, I know that the value is stored in the variable but the private sub routine only sees it as "", and I get a run-time error 13.

I don't have this issue with other private subs that reference public variables, so I'm not sure where this is going wrong.

Taking advice from an answer to a similar question, I took different approach and I wrote in my module:

Public Property Get Somesarname() As String

Somesarname = sarname

End Property

I then used 'somesarname' in my FilBox1 sub instead. That gets me a 'subscript out of range' error and 'somesarname' still showing up as " " when I hover the cursor over it.

I'm totally stumped now. What basic thing am I missing here?

Teamothy
  • 2,000
  • 3
  • 16
  • 26
KeithK
  • 25
  • 4
  • 1
    Start by adding `Option Explicit` at the top of all of your code modules (and set that as the default in VB options by checking "Require variable declaration". If you use that it will tell you about problems like `SARWorkbook` vs `SARWorkbk` – Tim Williams Nov 19 '19 at 20:52

1 Answers1

0

Simpler like this:

Public wbSAR As Workbook   '<< public variable in Module1 

'in ThisWorkbook module
Private Sub Workbook_Open()
    Set wbSAR = Workbooks.Add("C:\Users\...xlsm") 'Set the workbook reference
End Sub

Then later:

'If box is checked, a "Yes" string is entered into range object of new workbook wbSAR
wbSAR.Worksheets("COC Form").Range("B46").Value = IIf(FilBox1.Value, "Yes", "No")

EDIT: alternative approach using constants for wb path and name 

'Constants in module1
Public Const WB_PATH As String = "C:\Folder1\Folder2\"   
Public Const WB_NAME As String = "ThisIsIt.xlsm"   

'in ThisWorkbook module
Private Sub Workbook_Open()
    Workbooks.Add(WB_PATH & WB_NAME) 
End Sub
Then later:

'If box is checked, a "Yes" string is entered into range object of new workbook wbSAR
Workbooks(WB_NAME).Worksheets("COC Form").Range("B46").Value = IIf(FilBox1.Value, "Yes", "No")
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Using your code produced a "error '91': Object variable or With block variable no set" message. When I check the data type of `wbSAR` using `MsgBox TypeName(wbSAR)` it returns `nothing`. It should be `workbook` right? – KeithK Nov 20 '19 at 16:38
  • 1
    Yes - if that workbook_open code runs successfully then `wbSAR` should reference a notebook It's possible that some event is resetting your global variables. Some things to check: https://stackoverflow.com/questions/7041138/what-is-the-lifetime-of-a-global-variable-in-excel-vba/7043901#7043901 – Tim Williams Nov 20 '19 at 16:43
  • I removed the `end sub` statement from `Private sub workbook_open()` and it did the trick. "Yes" and "No" now show up in my new workbook. Otherwise, keeping this `end` statement would reset the `wbSAR` object to 'nothing'. – KeithK Nov 20 '19 at 17:13
  • That sounds odd - removing `End Sub` should have broken your code. It's only `End` by itself which causes a problem. – Tim Williams Nov 20 '19 at 18:16
  • Yes, you are right. It somehow worked though, but now I am back to the same problem. – KeithK Nov 20 '19 at 18:24
  • I don't see evidence of the 3 other problems listed in that post. I get no run-time error messages. – KeithK Nov 20 '19 at 18:34
  • If the workbook path and name is hard-coded in your `Workbook_open` procedure, can't you use that same information later elsewhere? You don't need to store it in a second place if you move it outside of the Open into a global constant for example. See my edit above – Tim Williams Nov 20 '19 at 18:49
  • To wrap this up, I only notice this problem if private subs are repeatedly activated at command line using the run button in VBE. That must somehow reset the global variable. – KeithK Nov 20 '19 at 18:57