Sheet names with spaces in VBA

hatstand

Well-known Member
Joined
Mar 17, 2005
Messages
778
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
You really need to explain what Ctrl.Tag is all about, what is in there, how does it get there?
 
Upvote 0
If your Tag includes the sheet name as well as a range reference, then use something like:
Code:
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
 
Upvote 0
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
 
Upvote 0
hatstand: From Excel VBA help: <style>@import url(/Office.css);</style><link disabled="" href="/MSOffice.css" type="text/css" rel="stylesheet">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?
 
Last edited:
Upvote 0
When you use the Ctrl.Tag value, add a single quote before and after the sheet name.
Code:
Ctrl.Tag = "'" & Ctrl.Tag & "'"
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top