+ Reply to Thread
Results 1 to 5 of 5

VBA issue incorporating active sheet name with check box reference

Hybrid View

  1. #1
    Registered User
    Join Date
    05-05-2009
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    68

    Question VBA issue incorporating active sheet name with check box reference

    I have form object check box's that I wish to control using a dynamic reference. The check box number is in the format "Check Box ?" the "?" is a number from 1 to 21 which happens to reflect the sheet name that it is connected with. The sheets are named with numbers starting with "1" up to "21"

    Here is my attempt at the code:

    Dim Box As String
    Dim Sheet As Sring
    Box = "Check Box "
    Sheet = ActiveSheet.Name
        
          Sheets("Index").Shapes(Box & Sheet).ControlFormat.Value = True
    I suspect I may need to re arrange the quotation marks as the basic syntax is is

        
          Sheets("SHEET NAME").Shapes("CHECK BOX NAME").ControlFormat.Value = True
    I need to place this code on all 21 sheets and rather than manually specify the Check Box name on each sheet I'd like the code to incorporate the sheet name in the check box reference if possible..
    Thanks in advance,

    Mick.
    Last edited by mick86; 09-16-2012 at 11:46 AM.

  2. #2
    Registered User
    Join Date
    05-05-2009
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    68

    Re: VBA issue incorporating active sheet name with check box reference

    I managed to work it out myself

    
    Dim Text, Sheet, CheckBox
        
        Text = "Check Box"
        
        Sheet = ActiveSheet.Name
        
        CheckBox = Text & " " & Sheet
    
    Sheets("SHEET NAME").Shapes(CheckBox).ControlFormat.Value = True
    Last edited by Cutter; 09-23-2012 at 08:32 AM. Reason: Removed whole post quote

  3. #3
    Registered User
    Join Date
    05-05-2009
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    68

    Re: VBA issue incorporating active sheet name with check box reference

    I tried applying this to a ActiveX check box (as opposed to a form object check box) but can't see m to get it to work

      Dim Text, Sheet, CheckBox
        Text = "CheckBox"
        Sheet = ActiveSheet.Name
        CheckBox = """ & Text & Sheet & """
     
    
    
          Sheets("Index").CheckBoxes(CheckBox).Value = False
    I also tried

      Dim Text, Sheet, CheckBox
        Text = "CheckBox"
        Sheet = ActiveSheet.Name
        CheckBox = Text & Sheet
     
    
    
          Sheets("Index").CheckBox.Value = False
    Last edited by mick86; 09-19-2012 at 12:42 PM.

  4. #4
    Registered User
    Join Date
    05-05-2009
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    68

    Re: VBA issue incorporating active sheet name with check box reference

    still haven't been able to work this one out

  5. #5
    Registered User
    Join Date
    05-05-2009
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    68

    Re: VBA issue incorporating active sheet name with check box reference

    any assistance greatly appreciated

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1