+ Reply to Thread
Results 1 to 7 of 7

For each checkbox in userform1

  1. #1
    Registered User
    Join Date
    12-08-2022
    Location
    Italy
    MS-Off Ver
    2016
    Posts
    3

    For each checkbox in userform1

    Hello to everyone.
    I created a userform (UserForm1), and inside this form I placed 20 textboxes and 20 checkboxes.
    Everytime I make the userform to appear, I need all the checkboxes to be false and all the listboxes to be empty
    One question at a time...
    I can write down 20 rows to set each checkbox value=false but I would like to have something better
    I tried
    For i = 1 To 20
    UserForm1.Controls("CheckBox" & Chr(64 + i)).Value = False
    Next i
    but it says runtime error, impossible to find the specified object
    These instructions are placed in the command button macro, just before the UserForm1.show line

    How can I fix the error?
    Thanks to everyone

    O.

  2. #2
    Forum Contributor
    Join Date
    10-02-2012
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    293

    Re: For each checkbox in userform1

    Please Login or Register  to view this content.
    Place the code in the Userform_Initialize event, not the button to open the Userform.

    Also not sure why you used Chr(), this will convert an integer into a ascii character.
    Last edited by PrizeGotti; 12-08-2022 at 06:25 AM.

  3. #3
    Registered User
    Join Date
    12-08-2022
    Location
    Italy
    MS-Off Ver
    2016
    Posts
    3

    Re: For each checkbox in userform1

    Hi prizeGotti, and thank you for the immediate reply !

    I was making some T&E, and I came out with this code

    Dim ctl As Control

    For Each ctl In UserForm1.Controls
    If TypeName(ctl) = "CheckBox" Then
    ctl.Value = False
    End If
    Next

    For Each ctl In UserForm1.Controls
    If TypeName(ctl) = "TextBox" Then
    ctl.Value = ""
    End If
    Next

    that is substantially identical to what you suggest.
    One question: I placed it in the UserForm_Activate to make sure it runs every time I open the UserForm...
    Is the Initialize run only the first time or every time?
    And if it runs all the time, then what is the difference between activate and initialize?
    I was lead to think
    Initialize = only the first time
    Activate = evey time the userform pops up...

    O.

  4. #4
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,009

    Re: For each checkbox in userform1

    It's looking for controls called CheckboxA, CheckboxB and so on.
    Everyone who confuses correlation and causation ends up dead.

  5. #5
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,009

    Re: For each checkbox in userform1

    Initialize occurs whenever the form is loaded, whereas Activate occurs every time the form is activated, whether that is because it has been shown or because you switched to it from another excel window. There wouldn't typically be much point to that code in the Initialize event unless you set the control values to something else at design time.

  6. #6
    Forum Contributor
    Join Date
    10-02-2012
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    293

    Re: For each checkbox in userform1

    Apologies, yes, I meant to say the UserForm_Activate event, had only just woken up.

  7. #7
    Registered User
    Join Date
    12-08-2022
    Location
    Italy
    MS-Off Ver
    2016
    Posts
    3

    [SOLVED] Re: For each checkbox in userform1

    Good morning and thanks to all helped solving the question !

+ 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. UserForm1 Code Issue
    By Sanjibghosh in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-05-2017, 08:56 AM
  2. Userform1 checkbox help
    By wesgermer in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-22-2011, 04:15 AM
  3. How do you open UserForm1 with a checkbox?
    By JustinMAS in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-06-2008, 01:57 PM
  4. Replies: 1
    Last Post: 05-11-2006, 11:25 AM
  5. UserForm1 not getting removed using VBA
    By Alok in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-03-2006, 10:25 PM
  6. Scrolling of UserForm1
    By frankosun in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-06-2005, 04:15 PM

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