+ Reply to Thread
Results 1 to 8 of 8

Can a loop within a loop refer to each others controls?

Hybrid View

  1. #1
    Registered User
    Join Date
    01-09-2015
    Location
    Ontario, Canada
    MS-Off Ver
    2010
    Posts
    12

    Can a loop within a loop refer to each others controls?

    Hi,
    Just as the title asks, wondering if its possible or if I have not set up the this code correctly.

    Trying to loop through 34 check boxes and check if they equal true (checked) and then at the same time loop through my 34 text boxes with some dates and additions.
    Was hoping this would work and save me a lot of typing or copying and pasting by just creating this once in a sub CheckBoxes and calling to it from the check boxes click events.

    Here is what I thought would work

    Sub Checkboxes()
        'Check box to put a date and time stamps in DateSprayed, ReEntry, and PHI text boxes on userform
    Dim i As Integer
    Dim x As Integer
    
    With Checklist
        For i = 1 To 34
            If Me.Controls("chk" & i) = True Then
                With Checklist
                    For x = 1 To 34
                        Me.Controls("txtDateSprayed" & x).Value = Now()
                        Me.Controls("txtReEntry" & x).Value = DateAdd("H", txtMaxReEntry, Me.Controls("txtDateSprayed" & x).Value)
                        Me.Controls("txtPHI" & x).Value = DateAdd("H", txtMaxPHI, Me.Controls("txtDateSprayed" & x).Value)
                    Next x
                End With
            Else
                With Checklist
                    For x = 1 To 34
                        Me.Controls("txtDateSprayed" & x).Value = ""
                        Me.Controls("txtReEntry" & x).Value = ""
                        Me.Controls("txtPHI" & x).Value = ""
                        'Format to dates and times
                        Me.Controls("txtDateSprayed" & x).Value = Format(Me.Controls("txtDateSprayed" & x).Value, "mmm-dd-yy - HH:MM")
                        Me.Controls("txtReEntry" & x).Value = Format(Me.Controls("txtReEntry" & x).Value, "mmm-dd-yy - HH:MM")
                        Me.Controls("txtPHI" & x).Value = Format(Me.Controls("txtPHI" & x).Value, "mmm-dd-yy - HH:MM")
                    Next x
                End With
            End If
        Next i
    End With
    End Sub
    Thanks to anyone who may shed some light on this.
    Last edited by frontypro4x; 01-19-2015 at 05:52 PM.

  2. #2
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Can a loop within a loop refer to each others controls?

    What happened when you tried it?
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  3. #3
    Registered User
    Join Date
    01-09-2015
    Location
    Ontario, Canada
    MS-Off Ver
    2010
    Posts
    12

    Re: Can a loop within a loop refer to each others controls?

    Nothing happens at all. I checked the check box and nothing is put into the textboxes
    By the way the 2 i's in the Dateadd function are wrong supposed to x's (just corrected that now)
    Last edited by frontypro4x; 01-19-2015 at 05:53 PM.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Can a loop within a loop refer to each others controls?

    Are the checkboxes linked to the textboxes?

    For example is chk12 linked to txtDateSprayed12, txtReEntry12 etc?

    Does this work?
    Sub Checkboxes()
        'Check box to put a date and time stamps in DateSprayed, ReEntry, and PHI text boxes on userform
    Dim i As Long
    
        For i = 1 To 34
            If Me.Controls("chk" & i) = True Then
                Me.Controls("txtDateSprayed" & i).Value = Now()
                Me.Controls("txtReEntry" & i).Value = DateAdd("H", txtMaxReEntry, Me.Controls("txtDateSprayed" & i).Value)
                Me.Controls("txtPHI" & i).Value = DateAdd("H", txtMaxPHI, Me.Controls("txtDateSprayed" & i).Value)
            Else
                Me.Controls("txtDateSprayed" & i).Value = ""
                Me.Controls("txtReEntry" & i).Value = ""
                Me.Controls("txtPHI" & i).Value = ""
                'Format to dates and times
                Me.Controls("txtDateSprayed" & i).Value = Format(Me.Controls("txtDateSprayed" & i).Value, "mmm-dd-yy - HH:MM")
                Me.Controls("txtReEntry" & i).Value = Format(Me.Controls("txtReEntry" & i).Value, "mmm-dd-yy - HH:MM")
                Me.Controls("txtPHI" & i).Value = Format(Me.Controls("txtPHI" & i).Value, "mmm-dd-yy - HH:MM")
            End If
        Next i
    
    End Sub
    If posting code please use code tags, see here.

  5. #5
    Registered User
    Join Date
    01-09-2015
    Location
    Ontario, Canada
    MS-Off Ver
    2010
    Posts
    12

    Re: Can a loop within a loop refer to each others controls?

    Are the checkboxes linked to the textboxes?

    For example is chk12 linked to txtDateSprayed12, txtReEntry12 etc?
    Hi Norie,
    No not that I know of.
    Your simplified code works great!! except it seems to ignore the date and time formatting. Any suggestions?

    Thank you both for your help on this!!

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Can a loop within a loop refer to each others controls?

    This is the only one part of the code that does formatting.
    Me.Controls("txtDateSprayed" & i).Value = Format(Me.Controls("txtDateSprayed" & i).Value, "mmm-dd-yy - HH:MM")
    Me.Controls("txtReEntry" & i).Value = Format(Me.Controls("txtReEntry" & i).Value, "mmm-dd-yy - HH:MM")
    Me.Controls("txtPHI" & i).Value = Format(Me.Controls("txtPHI" & i).Value, "mmm-dd-yy - HH:MM")
    Try moving that up to the 'true' part of the If statement.
            If Me.Controls("chk" & i) = True Then
                Me.Controls("txtDateSprayed" & i).Value = Now()
                Me.Controls("txtReEntry" & i).Value = DateAdd("H", txtMaxReEntry, Me.Controls("txtDateSprayed" & i).Value)
                Me.Controls("txtPHI" & i).Value = DateAdd("H", txtMaxPHI, Me.Controls("txtDateSprayed" & i).Value)
                'Format to dates and times
                Me.Controls("txtDateSprayed" & i).Value = Format(Me.Controls("txtDateSprayed" & i).Value, "mmm-dd-yy - HH:MM")
                Me.Controls("txtReEntry" & i).Value = Format(Me.Controls("txtReEntry" & i).Value, "mmm-dd-yy - HH:MM")
                Me.Controls("txtPHI" & i).Value = Format(Me.Controls("txtPHI" & i).Value, "mmm-dd-yy - HH:MM")
            Else
                Me.Controls("txtDateSprayed" & i).Value = ""
                Me.Controls("txtReEntry" & i).Value = ""
                Me.Controls("txtPHI" & i).Value = ""
            End If

  7. #7
    Registered User
    Join Date
    01-09-2015
    Location
    Ontario, Canada
    MS-Off Ver
    2010
    Posts
    12

    Re: Can a loop within a loop refer to each others controls?

    Yep that worked.
    Thanks again!!
    You guys know your stuff. This site is proving to be an awesome resource.

  8. #8
    Registered User
    Join Date
    01-09-2015
    Location
    Ontario, Canada
    MS-Off Ver
    2010
    Posts
    12

    Re: Can a loop within a loop refer to each others controls?

    Encountered a major flaw with calling the CheckBoxes sub in the check boxes click event set up.
    The code is being executed to all of the 34 checkboxes and textboxes each time you click any one, giving you the very last date and time in all of them.

    Thinking I might have to code each checkbox click event with its own relative textboxes to get around this.

    Unless of course if someone knows a different way?

+ 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] Refer sheet name by For Each loop error
    By Jul Stev in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-17-2014, 11:21 PM
  2. [SOLVED] refer to Textbox using a loop
    By mortphil in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-18-2013, 06:55 AM
  3. [SOLVED] Copy dynamically changing column and Paste using VBA Loop (Loop within Loop)
    By nixon72 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-12-2013, 12:46 PM
  4. Refer checkbox by name in a loop
    By Xtender in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-24-2006, 07:50 AM
  5. [SOLVED] How to loop through controls on a MultiPage
    By 42N83W in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-14-2005, 08:06 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