Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Sheet names with spaces in VBA

This is a discussion on Sheet names with spaces in VBA within the Excel Questions forums, part of the Question Forums category; Hi, I have been using the code below for a form. But now I need to change the sheet name ...

  1. #1
    Board Regular
    Join Date
    Mar 2005
    Location
    UK, Newcastle
    Posts
    666

    Default Sheet names with spaces in VBA

    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

  2. #2
    Board Regular pbornemeier's Avatar
    Join Date
    May 2005
    Location
    Virginia Beach, VA USA
    Posts
    2,450

    Default Re: Sheet names with spaces in VBA

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

    - Display worksheets using Excel Jeanie or HTML Maker
    - Test and validate results for all code on a copy of your worksheet!! How do you use the code you just found?
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes (use CODE to keep your code formatted)

  3. #3
    Board Regular
    Join Date
    Mar 2005
    Location
    UK, Newcastle
    Posts
    666

    Default Re: Sheet names with spaces in VBA

    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.

  4. #4
    Board Regular
    Join Date
    Feb 2009
    Posts
    245

    Default Re: Sheet names with spaces in VBA

    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

  5. #5
    Board Regular
    Join Date
    Mar 2005
    Location
    UK, Newcastle
    Posts
    666

    Default Re: Sheet names with spaces in VBA

    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?

  6. #6
    xld
    xld is offline
    Banned
    Join Date
    Feb 2003
    Location
    The Kingdom of Wessex
    Posts
    5,378

    Default Re: Sheet names with spaces in VBA

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

  7. #7
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    East Sussex
    Posts
    22,489

    Default Re: Sheet names with spaces in VBA

    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

  8. #8
    Board Regular
    Join Date
    Feb 2009
    Posts
    245

    Default Re: Sheet names with spaces in VBA

    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

  9. #9
    Board Regular pbornemeier's Avatar
    Join Date
    May 2005
    Location
    Virginia Beach, VA USA
    Posts
    2,450

    Default Re: Sheet names with spaces in VBA

    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?
    Last edited by pbornemeier; Mar 5th, 2009 at 07:07 AM.
    Phil

    - Display worksheets using Excel Jeanie or HTML Maker
    - Test and validate results for all code on a copy of your worksheet!! How do you use the code you just found?
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes (use CODE to keep your code formatted)

  10. #10
    Board Regular pbornemeier's Avatar
    Join Date
    May 2005
    Location
    Virginia Beach, VA USA
    Posts
    2,450

    Default Re: Sheet names with spaces in VBA

    When you use the Ctrl.Tag value, add a single quote before and after the sheet name.
    Code:
    Ctrl.Tag = "'" & Ctrl.Tag & "'"
    Phil

    - Display worksheets using Excel Jeanie or HTML Maker
    - Test and validate results for all code on a copy of your worksheet!! How do you use the code you just found?
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes (use CODE to keep your code formatted)

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com