+ Reply to Thread
Results 1 to 9 of 9

Help for Macro to Fill Columns Based on Tick Boxes

  1. #1
    Registered User
    Join Date
    04-04-2021
    Location
    France
    MS-Off Ver
    2016
    Posts
    4

    Help for Macro to Fill Columns Based on Tick Boxes

    Hello everyone,

    I need some help. I have tried to make a Macro, but it doesn't work as I want. I have put A link to the file below this message.
    What i wish is, when I press on the "A" box, the A column stay note filled whith black, and the other column (C5 to F14) are filled whith black. When I press again "A" the spreadsheet reset.
    When I press A and B, the column A and B stay note filled whith black, and the other column (D5 to F14). When I press C and D, the column C and D stay note filled whith black, and the other column (B5 to C14 and E5 to F14).

    I hope that you have understand my post (Sorry i'm French)

    Thank you

    PS : some cells need to stay in black

    Sans titre.png

    Please check out the rules on thread titles, which need to be detailed, not generic. I have amended your title today.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by Jos68; 04-04-2021 at 03:51 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,383

    Re: Help for Macro

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    04-04-2021
    Location
    France
    MS-Off Ver
    2016
    Posts
    4

    Re: Help for Macro

    Thanks, I edited my first post

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Help for Macro to Fill Columns Based on Tick Boxes


    Hi,

    a bit confusing, elaborate in details each case …

  5. #5
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Try this !


    With a well designed worksheet it may need a single tiny procedure for all the check boxes !
    So in order to do it you must execute the UsageUnique procedure just once then enjoy !
    If it's the expected result, Module1 is useless, to be deleted …

    According to your attachment as a VBA beginner starter to paste to the Feuil1 worksheet module :

    PHP Code: 
    Sub BlackWhite(N&)
            
    A$ = Choose(N"B5:B7,B9:B14""C5:C6,C8:C12,C14""D5:D10,D12:D14""E5,E7:E14""F6:F9,F11:F14")
            
    Range(A).Interior.ColorIndex + (Shapes("Coche" N).ControlFormat.Value 1)
    End Sub

    Private Sub UsageUnique()
        For 
    N& = 1 To Shapes.Count
            With Shapes
    (N)
                .
    Name "Coche" N
                
    .OnAction "'Feuil1.BlackWhite " "'"
            
    End With
                BlackWhite N
        Next
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon « Add Reputation » !
    Last edited by Marc L; 04-04-2021 at 09:09 AM. Reason: typo …

  6. #6
    Registered User
    Join Date
    04-04-2021
    Location
    France
    MS-Off Ver
    2016
    Posts
    4

    Re: Try this !

    Quote Originally Posted by Marc L View Post

    With a well designed worksheet it may need a single tiny procedure for all the check boxes !
    So in order to do it you must execute the UsageUnique procedure just once then enjoy !
    If it's the expected result, Module1 is useless, to be deleted …

    According to your attachment as a VBA beginner starter to paste to the Feuil1 worksheet module :

    PHP Code: 
    Sub BlackWhite(N&)
            
    A$ = Choose(N"B5:B7,B9:B14""C5:C6,C8:C12,C14""D5:D10,D12:D14""E5,E7:E14""F6:F9,F11:F14")
            
    Range(A).Interior.ColorIndex + (Shapes("Coche" N).ControlFormat.Value 1)
    End Sub

    Private Sub UsageUnique()
        For 
    N& = 1 To Shapes.Count
            With Shapes
    (N)
                .
    Name "Coche" N
                
    .OnAction "'Feuil1.BlackWhite " "'"
            
    End With
                BlackWhite N
        Next
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon « Add Reputation » !
    Hi,

    I have tried this, but it doesn't work

  7. #7
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Help for Macro to Fill Columns Based on Tick Boxes


    As it rocks on my side with your attachment so read again - but well this time ! - my previous post direction and apply …

  8. #8
    Registered User
    Join Date
    04-04-2021
    Location
    France
    MS-Off Ver
    2016
    Posts
    4

    Re: Help for Macro to Fill Columns Based on Tick Boxes

    I must be so noob, I have tried once again, but nothing happen.

    What is UsageUnique procedure ?

    I have attached the new file, with module 1 delete and you VBA code
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Help for Macro to Fill Columns Based on Tick Boxes


    You did not follow my direction as you did not execute the UsageUnique procedure which redesigns the worksheet …

+ 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. [SOLVED] Dynamic macro name / Concatenating macro name / Call a string variable as a macro name
    By vwhite in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-18-2020, 03:36 AM
  2. [SOLVED] macro showing error ( macro not found ) while trying to execute macro every 1 minute
    By prabhuduraraj09 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-16-2018, 07:58 PM
  3. [SOLVED] Define variable in macro than calling that macro inside another macro
    By jakopak in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-21-2015, 10:58 AM
  4. [SOLVED] Macro to show Which macro didnt work in a nested macro
    By akhileshgs in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 06-10-2013, 03:21 AM
  5. Perform macro "on open" specific file- store macro in Personal Macro Workbook?
    By thompssc in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-17-2012, 12:38 PM
  6. lookup macro, solver macro, realtime macro
    By xelhelp in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-02-2011, 06:14 PM
  7. Cannot find macro error when running a macro from a macro in a diffrent workbook.
    By Acrobatic82 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-05-2010, 09:22 AM

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