+ Reply to Thread
Results 1 to 2 of 2

Checkbox to edit specific sheets when macro is run

Hybrid View

rose8693 Checkbox to edit specific... 08-19-2021, 08:16 AM
NoSparks Re: Checkbox to edit specific... 08-19-2021, 05:01 PM
  1. #1
    Registered User
    Join Date
    04-10-2019
    Location
    VA
    MS-Off Ver
    Excel 2010
    Posts
    23

    Question Checkbox to edit specific sheets when macro is run

    I have a macro that updates all the sheets named in my workbook. Which is great on setup, but for later edits I'll only need certain sheets to be updated, as this will be a personnel schedule, so I'll be adding and removing people fairly often.
    Enter the checkbox so I can select which sheets I want the Macro to run on.
    I just don't know how to integrate my current code with the check box aspect for the array.

    Please if you geniuses would be kind enough to help me figure this out. I'm sure I'm over thinking this. The workbook should be attached.

    Thank you
    Attached Files Attached Files
    Last edited by rose8693; 08-20-2021 at 01:11 PM.

  2. #2
    Forum Contributor
    Join Date
    08-09-2012
    Location
    British Columbia
    MS-Off Ver
    Excel 2010
    Posts
    190

    Re: Checkbox to edit specific sheets when macro is run

    I ran this macro to assign a linked cell to each checkbox, uses the cell the checkbox is on, you can set the text color to match the cell colors so the true and false are not visible.
    Sub LoopThroughFormCheckboxes()
        Dim chkbox As CheckBox
    For Each chkbox In ActiveSheet.CheckBoxes
        With chkbox
            .LinkedCell = .TopLeftCell.Address
        End With
    Next chkbox
    End Sub
    Altered your sub to this.
    Hopefully you can follow the comments
    Sub AddMembers()
    
    Dim rng As Range, cel As Range, sStr As String
    Dim Ws As Worksheet, wksheets As Variant
    Dim i As Long, c As Range
    
    'Loop through each check box linked cell
    Set rng = Sheets("dates").Range("J2:J13")
    
    ' build a string of month names
    For Each cel In rng
        If cel.Value = True Then
            sStr = sStr & "," & MonthName(cel.Row - 1, True)
        End If
    Next cel
    
    ' split the string to get array of month names
    wksheets = Split(Mid(sStr, 2), ",")
    
    ' loop through the array of month names
    For i = LBound(wksheets) To UBound(wksheets)
        On Error Resume Next    'in case sheet doesn't exist for that month
        Set Ws = Sheets(wksheets(i))
        On Error GoTo 0         're-instate error notifications
        
        If Not Ws Is Nothing Then   'if that worksheet does exist
            
            'test members
            Sheets("dates").Range("f2:g11").Copy Ws.Range("a4")     ':a10") '<~~~ mismatched range sizes
            
            For Each c In Ws.Range("a4:a10")
                If c.Value = Sheets("dates").Range("I2") Then
                    c.EntireRow.Hidden = True
                Else
                    c.EntireRow.Hidden = False
                End If
            Next
        End If
    Next i
    
    End Sub
    Hope that helps

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Edit VBA script to work in multiple specific sheets instead of just one
    By GregM56 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-11-2019, 01:32 PM
  2. Macro: Print hyperlinks in sheets, selected by checkbox.
    By MalcolmXcel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-20-2017, 11:54 AM
  3. Add, edit and clear macro buttons between sheets
    By chris1089 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-17-2015, 01:16 PM
  4. Replies: 9
    Last Post: 07-31-2014, 09:56 PM
  5. Macro to edit variable text in a specific cell
    By westic in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-01-2013, 10:19 AM
  6. Macro or VBA code to update non primary sheets based on checkbox
    By jbpar5s in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-02-2013, 06:19 PM
  7. Checkbox and Macro to delete specific data in corresponding row
    By doubleeinc in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-05-2012, 01:55 PM

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