Read and write to UserForm from filtered list. 2

[neiljabba]

Hello all

I have reached a desperate point which I would really appreciate help on.

The code below was orginally used on an unfiltered list with the data populating it spread on separate worksheets. It worked without any problems.

I have now centralised the data on one sheet for easier management and filter for what I need. The problem I have is that I cant make it populate the form from the filtered list and certainly cant write back to the vacant cells relevant to it.

There is another issue!!! The code now needs to identify if there is a vacant cell next to the entry and then if so write as normal if not insert a new column to accomodate the data. The new columns would be in a named range of columns e.g. "Register".

Therefore it needs a check to make sure that there is space and because its a filtered list make sure that any other filtered option follows the same process i.e only inserts a new column if no blank available. That means if one instance of the filter needed a new column the others would not until they filled the blanks created by it.

Anyway heres the code



Public Sub CommandButton1_Click()'appears on form

Let i = 0
Let r = 5

'inserts column for updated data including current date
' The date would be replaced by a comment added to each cell written to showing the current date
Columns("D").Select
Selection.Insert Shift:=xlToRight
Range("D3").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
copieddate = Cells(3, 4).Value ' copies the date so that the fomula doesnt cause problems
Cells(3, 4) = copieddate
Range("D333").Select
'obviously for appearance purposes only the follwing formats the column
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

'loop to insert marks as present or absent dependent on checkbox value and whether its there or not
For i = 1 To 30

If Me.Controls("CheckBox" & i).Value = True Then Cells(r, 4) = "/" Else Cells(r, 4) = "A"
If Me.Controls("CheckBox" & i).Visible = False Then Cells(r, 4) = ""

r = r + 1
Next i


UserForm1.Hide

End Sub
Public Sub UserForm_Initialize()


Let i = 0 'index for checkbox
Let r = 5 ' index for row number


'loop to define checkbox caption values if false then no checkbox
For i = 1 To 30
If Cells(r, 1) = "" Then Me.Controls("CheckBox" & i).Visible = False Else Me.Controls("CheckBox" & i).Caption = Cells(r, 1) & " " & Cells(r, 2)
r = r + 1
Next i

End Sub

This may be alot to ask but it has reached the final piece in a big jigsaw so to speak.

Many thanks in advance.

Neil

 

[000Steve000]

This checks which rows are hidden......

For i = 1 To 33
       If Worksheets("Sheet1").Rows(i).Hidden = False Then
            MsgBox "Not hidden"
       Else
            MsgBox "Hidden"
       End If
Next i

Hope it helps.

 

[000Steve000]

This inserts the cells. Its a bit inelegant but it works.

For i = 1 To 33
    CurrentCell = "D" & Trim(Str(i))
    If Worksheets("Sheet1").Rows(i).Hidden = False Then
        'MsgBox "Not hidden"
        CurrentRange = "D" & LTrim(Str(i)) & ":IU" & LTrim(Str(i))
        Range(CurrentRange).Select
        Application.CutCopyMode = False
        Selection.Cut
        Moveto = "E" & LTrim(Str(i))
        Range(Moveto).Select
        ActiveSheet.Paste
    Else
            'MsgBox "Hidden"
    End If
Next i

OK?

 

[neiljabba]

Cheers Steve

I understand the principle so i will try to interpret and adapt and then let you know.

Thanks for your time so far I may need to get back to you!!

Thanks again.

Off to test!!!

Neil

 

[neiljabba]

Sorry one more thing

I assume as you have helped me identify the hidden cells i can just replace the code i had when they were visible?

Cheers

Neil

 

[xlbo]

Neil - have a look at the SPECIALCELLS method - particularly the xlcelltypeVISIBLE

this would be used like this:

For Each c In Range("A1:A1000").SpecialCells(xlCellTypeVisible)

'Do stuff

Next

This will loop through the unhidden rows only within rows 1 to 1000

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 Geoff

Still idiot proofing a couple of other bugs.

Cheers

Neil

 

[xlbo]

They'll just create "better" idiots though ;-)

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]

Cheers guys but

I have been able to set up the 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

 

[xlbo]

not 100% sure but is this what you are after but this increments a variable only if the row is NOT hidden:

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

just need to seperate the logic test and incrementer

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]

Cheers Geoff

Sounds exactly what I need.


Ill give it a go.

Thanks again.

Neil

 

[neiljabba]

Thanks again Geoff but....

I still have the same problem. Basically I need one variable to run through the fitered list and increment the row value AND I need a second independent of the row BUT conditional on the presence of a visible row as its indicator to increment.

Problem also is that I seem to get all the invisible checkboxes not the ones starting with CHeckbox1 for the first visible row and then 2 for the next. This depends on my filtering as to where the caption comes from but should according to my logic test only delete the checkbox if no value is present, which in a filtered list the elements of that list are all there, except maybe at the end of the count if the list is shorter by one or two elements.

Is it likely that the invisible elements are being counteed as blanks hence the deletion of the boxes or is it still the variable problem?

Many thanks again

Cheers

Neil

 

[xlbo]

this may solve the 1st issue - not sure about the visible / invisible stuff:

lRow = cells(65536,1).end(xlup).row
for r = 1 to lRow
If Worksheets("Sheet1").Rows(r).Hidden = False Then
   'stuff happens here
   i = i + 1   
Else 
   'Other stuff here
end if
next r

r will increment in 1's from 1 to the last row value
i will increment in 1's for each VISIBLE row

So - if you were looking at rows 1 to 3 and row 2 was hidden:

Row r i
1 1 1
2 2 1
3 3 2

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]

Cheers Geoff one more but...

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?Or just annoying? Sorry to be a pain.

Cheers

Neil

 

[xlbo]

ok - think I understand

You are looping through a filtered list

Where you find a visible row, you want to apply the cell value to the caption of a checkbox on a form

the checkboxes all exist on the form but you don't want blank spaces between checkboxes

i = 1
For r = 3 To 14
If Worksheets("Sheet1").Rows(r).Hidden = False Then
 [COLOR=green]'Row is visible - add caption to next available checkbox[/color]
 Me.Controls("CheckBox" & i).Caption = Cells(r, 1) & "  " & Cells(r, 2)
 [COLOR=green]' increment i so it references the next checkbox along next time[/color]
 i = i + 1
 [COLOR=green]'this part should function fine[/color]
Else
  [COLOR=green]'don't need the next line at all as you want to hide at the end so leave this bit blank
  'Me.Controls("CheckBox" & i).Visible = False[/color]
End If
Next r
[COLOR=green]'After this, i should be at the max checkbox number that you have used so
'Clear up[/color]

For x = i+1 to 14 'ie from the next checkbox left over
  Me.Controls("CheckBox" & i).Visible = False
next x

hope this makes sense

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 again Geoff

I seem to have got it, but not without your 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

Thanks again

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

Have a couple of stars.

Cheers

Neil

 

[xlbo]

just realised - 14 should be 11 (ie 14-3)


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]

Seems we both realised my stupidity at the same time!!!! Its amazing what a good discussion can do to focus the mind!!

Thanks again Geoff

Cheers

Neil

 

[xlbo]

no probs - glad you managed to overcome the issue in the end ! Always like a nice little logic challenge !!

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]

Hello again.

Everything is working fine now Ill post the complete code shortly but how can I reset all the values. I have added a For Next to clear everything but this only works when I RUN the initialise after I have pressed STOP in the editor.

IS there something Im missing.

Cheers

Neil