Is this looping impossible?!! 2

[neiljabba]

Hello all

Thanks to a previous thread I have been able to set up the following form to have its checkbox captions defined as visible elements of a filtered list.

However due to what I need it to do it spreads the visible checkboxes unevenly across the form obviously because it only shows a checkbox if a visible element of the list is present.

What it should do is take the visible elements, skipping the hidden and not incrementing the variable, and populate them starting with the first checkbox and working through until no more values are left. When that is the case then the checkboxes are made hidden.

I hope this makes sense and if anyone can help I have tried

* setting another variable that goes down one if no value is present and using that as index for checkboxes. This got a bit crazy for me and out of range errors occured.

*defining an array of the values. This worked but did not populate from the first still as somewhere in the array was a blank value that it still stored and used. The main reason this was a problem was the above.

It all kept coming down to the same problem irrespective of the solution I tried I couldnt either:

*keep the variable associated with checkbox static if the element was hidden
*send the variable down if the element was hidden

Sorry for the length of explanation, but I know what I want in words just cant code it.

Anyway here are two of my efforts, done using a form with 10 checkboxes, but a solution populating cells would be fine as I could recode that.


This version works but only gets the first value as the first checkbox caption and then goes wrong

Sub UserForm_Initialize()



Let r = 3 ' this is correct

For i = 3 To 10
cb = (r - 3) + 1


If Worksheets("Sheet1").Rows(r).Hidden = False Then Me.Controls("CheckBox" & cb).Caption = Cells(r, 1) Else Me.Controls("CheckBox" & cb).Visible = False


r = r + 1

Next i


End Sub


This version works as well but does not put the first value in the first checkbox caption

Sub UserForm_Initialize()



Let r = 3 ' this is correct

For i = 3 To 10



If Worksheets("Sheet1").Rows(r).Hidden = False Then Me.Controls("CheckBox" & i).Caption = Cells(r, 1) Else Me.Controls("CheckBox" & i).Visible = False


r = r + 1

Next i


End Sub


Cheers

Neil

 

[SkipVought]

Neil,

If you want to dynamically position a control, you must use the Left, Top, Width & Height properties.

So you'd select a variable to, for instance, represent the left position of the reference control and assign that value to the Left property.

Then you would incriment the left variable by the Width property of the control plus some spacing (in pixels) value.

Hope this helps

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[neiljabba]

Thanks Skip

Unfortunately its still not quite what I need.

Im not concerned about the positioning of the boxes I understand how that might help me, but I need all boxes from 1 to 30 available for display. Its just that as the loop goes round i only want to collect the visble elements of the filtered list, of which there will be a maximum of 30, minimum of 28, and make sure those elements populate the caption starting from checkbox1 going through to 30, in the right order.

Its just that each time the loop increments the value of the checkbox index does too. Do I need another IF THEN option to make sure Im only collecting the visible elements with values or is there another looing option i.e. DO UNTIL REPEAT UNTIL that might be better?

Sorry to be a pain, but this is the last part of this project and it used to work perfectly with an unfiltered list, I have just changed the way I manage the content of the sheets.

Many thanks

Neil

 

[SkipVought]

First you stated

it spreads the visible checkboxes unevenly across the form

So I suggested a way to control the position.

Then you stated

Im not concerned about the positioning of the boxes

Just what are you asking for?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[neiljabba]

Sorry for any confusion skip

I need any visible element of the filtered list to populate the caption starting with the first checkbox, not with the Checkbox that the variable value has reached.

I.e. if Row 1 was hidden my row variable would be 1 and the Checkbox index variable would be 1
If Row 2 was visible both variables would be 2 but i need it populate the caption on Checkbox1 not 2 as it currently does.

Only problem is I need one variable to increment the row and one to keep tabs on the Checkbox index/number.

I have since tried a For Each using the special cells method visible property but this finds hidden and visible cells in confusing randomness for some odd reason.

So if theres a way to increment two variables one as a count and one independently/conditional on the visible protperty of the cell thats what I need.

Thanks for your help so far.

Cheers

Neil

 

[PHV]

a way to increment two variables one as a count and one independently/conditional
Something like this ?
c = 1
For r = r1 to r2
If your condition here Then
your code here
i = i + 1
End If
Next r

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[neiljabba]

Cheers PHV got that in a reply to my original post on the form problem itself.

Just realised however that Ive missed a blindingly obvious problem!!!

The checkboxes that are hidden should only be those left at the end of the filtered list

I.e. If only rows one and three are present then add their values to checkboxes 1 and 2 then remove the others. Of course the code removes them sequentially so maybe I need THREE variables. Heres the code as it stands.



Sub UserForm_Initialize()

Dim cbCap As String

'Let r = 3 ' this is correct
Let i = 1

For r = 3 To 14

If Worksheets("Sheet1").Rows(r).Hidden = False Then
Me.Controls("CheckBox" & i).Caption = Cells(r, 1) & " " & Cells(r, 2)
i = i + 1


Else


HERES THE PROBLEM ONLY THE BOXES LEFT SHOULD BE DELETED NOT AS A SEQUENCE FOLLOWING ON FROM THE ONES VISIBLE BECAUSE OF THE CONDITION
Me.Controls("CheckBox" & i).Visible = False

End If

Next r

End Sub


Clear as mud?

Cheers

Neil

 

[PHV]

Something like this ?
i = 1
For r = 3 To 14
If Worksheets("Sheet1").Rows(r).Hidden = False Then
Me.Controls("CheckBox" & i).Caption = Cells(r, 1) & " " & Cells(r, 2)
i = i + 1
End If
Next r
While i <= NumberOfCheckBoxes
Me.Controls("CheckBox" & i).Visible = False
i = i + 1
WEnd

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

For r = 3 To 14
    Me.Controls("CheckBox" & i).Visible = NOT Worksheets("Sheet1").Rows(r).Hidden  
   i = i + 1
Next r

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[xlbo]

PHV - that's almost identical to the code I've just posted in the other thread on this - used a for next construct rather than the While but I think your is more elegant

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[neiljabba]

Thanks Skip I got it.
I seem to have got it, but not without everyones(you and Geoff) help now I need to insert checked values back to the cells but that should be much easier now Ive cracked this.
Heres the code to populate the form.

Public j As Integer
Public r As Integer

Sub UserForm_Initialize()

Let i = 1

'loop to find the visible cells and then populate the checkbox captions with their values

lRow = Cells(65536, 1).End(xlUp).Row
For r = 3 To lRow
If Worksheets("Sheet1").Rows(r).Hidden = False Then
Me.Controls("CheckBox" & i).Caption = Cells(r, 1) & " " & Cells(r, 2)
i = i + 1

End If

Next r

'this section removes the remaining captionless chekcboxes change the j condition for more boxes
Let j = i
If j = 12 Then End

For k = j To 12 'change this with the j value

Me.Controls("CheckBox" & k).Visible = False

Next k


End Sub

No doubt yours is more elegant than mine too Skip, but it works so Im not going to touch it just yet.

Thanks again

Ill keep you posted on the outcome of the writing back and inserting columns cheers!!!

Have a couple of stars.

Cheers

Neil