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

 

[hatstand]

Hi,

I really appreciate the help form everyone.

Unfortunately I’m totally lost. I’m no programmer and am really trying to learn, but still can’t get it to work.

 

[pbornemeier]

Post a bit more of your code and explain what you are trying to do with it.

 

[hatstand]

Here is all of the code for the form:

As you may have guessed, I didn't write these! But I'm happy to try and understand what they do, for the future.

Private Sub UserForm_Activate()
Call Load_Controls
End Sub
'--------------------------------
Private Sub Btn_OK_Click()
Dim Ctrl As Control
For Each Ctrl In KeyPoints.Controls
If Ctrl.Tag <> "" Then
Range(Ctrl.Tag).Value = Ctrl.Text
End If
Next Ctrl
Btn_Cancel_Click
End Sub
'-----------------------------------
Private Sub Load_Controls()
Dim Ctrl As Control
For Each Ctrl In KeyPoints.Controls
If Ctrl.Tag <> "" Then
Ctrl.Text = Range(Ctrl.Tag).Text '***
End If
Next Ctrl
End Sub
'---------------------------------------
Private Sub Btn_Cancel_Click()
Unload Me
End Sub

 

[pbornemeier]

Assuming that the code is from the Keypoints userform....
When that form is loaded, it displays some list boxes.
For those listboxes that have a value in their .Tag field
that value identifies an address which contains the data that is displayed
in the listbox.

When the OK button is pressed, the values that are now in the listboxes
are copied back to the addresses specified in the .Tag values

If the address is invalid, the 1004 error is raised.

Valid addresses for the .Tag would inlcude those like:
A1
Sheet1!A1
'Sheet Name With Spaces'!A1

Note the single quote around the worksheet name (Sheet Name With Spaces) in the last example.

See what the .Tag values are for all of the controls and make sure they are valid addresses.

 

[westconn1]

i guess the tag property has been set at designtime
put the line
msgbox ctrl.tag
into the code to display the content of the tag property

If Ctrl.Tag <> "" Then
msgbox ctrl.tag
             Ctrl.Text = Range(Ctrl.Tag).Text '***
        End If
    Next Ctrl

 

[hatstand]

I still can't get it to work, so I guess I'll start fromscratch and try another way of doing the form.

Thanks for the help I really appreciate it.