Pull in Earlier Value Using Concatenation

Question

I have code that works in some situations but not in others.

I have this code:

 Public Sub ListBox2_LostFocus() 
ListBox2.Height = 15 
   With ListBox2 
   ThisIs_Sheet1_Test = "'" 
   For i = 0 To .ListCount - 1 
        If .Selected(i) Then 
        ThisIS_Sheet1_Test = ThisIs_Sheet1_Test & .List(i) & "','" 
    End If 
Next i 
End With 
ThisIs_Sheet1_Test = Left(ThisIs_Sheet1_Test, Len(ThisIs_Sheet1_Test) - 2) 
End Sub 

that produces "ThisIs_Sheet1_Test". So when I run the code below, it gives me the selected values in the listbox.

Public Sub dummy() 
Dim SheetName As String 

SheetName = ActiveSheet.Name 

Sheets("Sheet1").Range("I5", "I5") = ThisIs_Sheet1_Test

End Sub

However, when I use

Sheets("Sheet1").Range("I5", "I5") = "ThisIs_" & SheetName & "_Test"

I get the value of "ThisIs_SheetName_Test" which is obviously not what I'm looking for.

How to I bring in the value and then have the VBA recognize that it should be pulling in the earlier value?

Answer 1

You are using ThisIs_Sheet1_Test as a variable to hold a value (even though you don't explicitly declare the variable). You can't just assemble a string with the same name and expect it to somehow know what you want (to get the value held in the variable).

I'm assuming you are trying to do this because each sheet might have different values to track. In that case, here are a few options:

• Explicitly declare a public string array, with enough values for each of your sheets. Then you can track each of your values using the index number of the worksheet you are on.
• Instead of using a variable, pick a hidden spreadsheet cell to hold the value, then you can just use Sheets("Sheet1").Range("Z100").Value or something similar. This is a bit "hacky", and also fragile (because the user can overwrite the cell if it isn't locked or hidden), but might work the best without a bunch of other code.

• Use "custom properties" of the worksheet. This would look something like:

  Dim mySheet As Worksheet
  Set mySheet = ActiveSheet
  
  
  'mySheet.CustomProperties.Item(1).Delete
  mySheet.CustomProperties.Add "ListboxValues", "one,two,three"
  
  
  MsgBox mySheet.CustomProperties.Item(1).Name & " = " & _
         mySheet.CustomProperties.Item(1).Value
  

If you only use a single custom property like my example, they are pretty easy to use. Once you have more than one, then you have to loop through to find the custom property with the correct name, and use that.

Answer 2

Looks like you are using my solution to earlier question verbatim. Perhaps if you want to use valu of an variable that you set in in one function in another you need to declare it out side..

So move your Dim SheetName As String to bigining of the module, all the way up to first line.