Sheet names with spaces in VBA

[hatstand]

Hi,

I have been using the code below for a form. But now I need to change the sheet name to have spaces. But when I do. I comes up with the Run Time 1004 error on the “Ctrl.Text = Range(Ctrl.Tag).Text '*** “ line.

Why is it that this line is not allowing me to have spaces?

Private Sub Load_Controls()
Dim Ctrl As Control
For Each Ctrl In UserForm1.Controls
If Ctrl.Tag <> "" Then
Ctrl.Text = Range(Ctrl.Tag).Text '***
End If
Next Ctrl
End Sub

[pbornemeier]

What are some examples of Ctrl.Tag that worked and some that did not work?

[hatstand]

I'm not very good at VB, in fact rubbish. So I'm not sure what the Tag does. As far as I'm aware the Ctrl.Tag looks in the Tag in the properties and returns the value in the sheet and cell name listed.

It works fine if the the sheet name is one word, but try to insert spaces and it stops working.

[westconn1]

the code you posted does not use the sheet name in any way, and is actually working with the controls on a userform

If Ctrl.Tag <> "" Then
Ctrl.Text = Range(Ctrl.Tag).Text '***

in the first line ctrl.tag is the tag property of each control
in the second line ctrl.tag represents a range address, so you need to know the content of the tag property you also probably need to qualify the range with the sheet name
ctrl.text = sheets("sheet name").range(ctrl.tag).text
the tag property may also include the sheet name

[hatstand]

Thanks for the reply.

I used you line instead of (Ctrl.Text = Range(Ctrl.Tag).Text '***) and it still won't allow sheet names with spaces.

Would it be easier for me to use another method to populate my user form and allow the user to enter there own values?

[xld]

You really need to explain what Ctrl.Tag is all about, what is in there, how does it get there?

[RoryA]

If your Tag includes the sheet name as well as a range reference, then use something like:

Dim lngBang as Long
lngBang = InStr(1, Ctrl.Tag, "!", vbTextCompare)
If lngBang > 0 then
Ctrl.Text = Range("'" & left(Ctrl.Tag, lngBang - 1) & "'" & mid(Ctrl.Tag, lngBang)).Text '***                      
Else
Ctrl.Text = Range(Ctrl.Tag).Text
End If

[westconn1]

you need to specify the correct sheet name for the range that contains the data to populate the ctrl, you need to check the tag property to see if it already contains the sheetname
range("sheet1!a2") is the same as sheets("sheet1").range("a2")

so it is possible that the tag property specifies a specific sheet name, this can be fixed either by changing the content of the tag property or by using the replace function in the code
ctrl.text = range(replace(ctrl.tag, "oldsheetname" , "sheet name with space")).text
you will have to figure out the correct sheet names

[pbornemeier]

hatstand: From Excel VBA help: Use the Tag property to assign an identification string to an object without affecting other property settings or attributes.
For example, you can use Tag to check the identity of a form or control that is passed as a variable to a procedure.

.Tag is normally empty. What are doing with the information you put in there?

[pbornemeier]

When you use the Ctrl.Tag value, add a single quote before and after the sheet name.

Ctrl.Tag = "'" & Ctrl.Tag & "'"