Concatenation yields error with underscore

Question

I am trying to create a macro that brings in the name of the sheet and combine it with text. For example, for sheet one, I want it to say "ThisIs_Sheet1_Test" in I5 of Sheet1. There are several sheets but it should work for all of them.

What is wrong with my code? I think the underscore might be ruining it all. Here's what I have:

Dim SheetName As String

Public Sub CommandButton1_Click()

SheetName = ActiveSheet.Name

Sheets("Sheet1").Range("I5", "I5") = ThisIs_" & SheetName.text & "_Test
Sheets("Sheet2").Range("H5", "H5") = ThisIs_" & SheetName.text & "_Test
Sheets("Sheet3").Range("G5", "G5") = ThisIs_" & SheetName.text & "_Test

End Sub

This question has been forwarded to http://stackoverflow.com/questions/2949739/pull-in-earlier-value-using-concatenation

Answer 1

looks like a quoting problem. ThisIs_ and _Test are strings, right? So the quotes should be around them, not around & SheetName.text &

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

Answer 2

In addition to the missing quotes, SheetName is a string, not an object, so it won't have a Text property. Did you want the name of the sheet to change as the sheet changes? You need this:

Private Sub CommandButton1_Click()

    Dim ws As Worksheet

    For Each ws In Me.Parent.Worksheets
        ws.Range("I5").Value = "ThisIs_" & ws.Name & "_Test"
    Next ws

End Sub

Answer 3

I am able to get it to work...sort of.

Now, it displays ThisIS_Sheet1_Test in Sheet1, etc.

However, I have it set up to pull data from a listbox into a function which I called ThisIS_Sheet1_Test. I figured that once I got it to display the name, it would pull the function in. Here's what I have above:

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

How come when I get the text I want it doesn't translate into the function I thought it would.

Answer 4

can you take few minutes to compile your code (in VBA Window, Debug->Compile VBA Project) before looking for other's help?? That would have screamed about your missing quotes, using .Text on a string variable etc.

Public Sub dummy()
Dim SheetName As String

SheetName = ActiveSheet.Name

Sheets("Sheet1").Range("I5", "I5") = "ThisIs_" & SheetName & "_Test"
Sheets("Sheet2").Range("H5", "H5") = "ThisIs_" & SheetName & "_Test"
Sheets("Sheet3").Range("G5", "G5") = "ThisIs_" & SheetName & "_Test"

End Sub

Underscore carries a special meaning in VBA/VB world. Its code concatination (meaning if your code is too long and you want to split it across two lines then you put a space underscore ( _) and continue with next line. And also Dick Kusleika is right about object/string. Only for objects you will have differnt peopreties (.Text means you are asking for Text property of that object), and usually that Text property would be of String type. here you already have a String, and you just use it as it is.