Trying to determine if certain fields are unhidden.

Hi,

I have Excel 2003 and I'm using check boxes to determine which fields to be shown. When these checkboxes are ticked, the row(s) are unhidden and when finalized copied to clipboard.

I was wondering if it would be possible to have "profiles" that determine which rows MUST be unhidden/selected (whether through seeing if these rows are hidden or whether its respective checkbox is marked true).

I have programmed before with VB.Net and I am somewhat familiar with the .Net/C# syntax, but I'm not sure how I'd do this with VBA.

Can I have a combo box with each line representing a "profile" and then with that profile having hard-coded (or preferably referencing a few rows in a sheet)?

If so how would I compare with them?

Just use a for loop and output in a textbox what's False?

How can I do an output / seperate form/window that will pop up?

Thanks.

a 'profile' could be:

[VBA]
Range("A5:A10,A24,A16,A20:A24").entirerow.hidden =false
[/VBA]

Quote:

---

Originally Posted by snb

a 'profile' could be:

[vba]
Range("A5:A10,A24,A16,A20:A24").entirerow.hidden =false
[/vba]

---

Thanks, that looks fantastic.

Could I then just iterate over the range with a for loop and add the missing rows, or a "if .... *.hidden = True Then txtMissing.Text" and add to it ?

I don't have the code on me right now to look, but thank you for the help.

You won't need any loop, that's built in in a non-contiguous range.

Quote:

---

Originally Posted by snb

You won't need any loop, that's built in in a non-contiguous range.

---

Alright, how would I go about selecting the ones that are hidden in that range?

I'm thinking of a function that is ran when the finish button is clicked which will pop up a user form saying what values are missing.

Also, right now all my code is like:

(pseudo code because I don't have access to this code for now)
Sub CheckboxNAME_click
if checkboxNAME.value = True Then:
sheet_rows(23).hidden = False
else
sheet_rows(23).hidden = true

Is there any way I can I skip reading from the values from the sheet? for every checkbox I'm adding there's like 7 lines of code (for when it's checked/unchecked, and assigning a value of 1 to the third column).

Is there any way I can assign the value direct to the checkbox? I'm used to VB.net where I'm able to do this, and I really don't know the functionality of VBA that well. It's just annoying having to find and reference every checkbox.

Thank you for your help

I think posting a workbook could be benificial to our understanding of your requirements.

The 'code' you posted (please use code tags) can be substituted by a oneliner:
[vba]
sheets(1).rows(23).hidden = not checkboxNAME
[/vba]

Thanks, the code I have (originally) is below:

[vba]
Private Sub cbApplication_Click()

If Me.cbApplication = True Then
ThisWorkbook.Worksheets("Note").Rows("6").Hidden = False
Me.cboApplication.Value = "1"
ThisWorkbook.Worksheets("Note").Range("C6").Value = "1"
Else
ThisWorkbook.Worksheets("Note").Rows("6").Hidden = True
ThisWorkbook.Worksheets("Note").Range("C6").Value = ""
End If
End Sub
[/vba]

If it is ticked, the line is unhidden, and a value of 1 is assigned to the third column (second column is "x") so the output looks like:

Stuff x 1
Items x 2
etc.

It's reset on being ticked/unticked.It looks like I'm always going to need the if statements since the cbo (the combo box with quantity) shouldn't have a value when it's not ticked.

What code would I need to create a combo box with say "option1, option2, option3" where each option is tied with a

[vba]Range("A5:A10,A24,A16,A20:A24").entirerow.hidden =False [/vba]

and for each row that is hidden is printed into a messagebox ?

Can you please reread my last post ?

I am not able to post a workbook.

Of course you are... Do not underestimate yourself. ;)

Quote:

---

Originally Posted by snb

Of course you are... Do not underestimate yourself. ;)

---

I mean that in the sense that what is posted in those workbooks is somewhat sensitive and I can't imagine my work would like it being posted.

Nobody is asking that. Post a sample workbook for clarification without any sensitive data......

Here's a sample workbook based on the same concept.

It opens up with the image below. Each combo box refers to named ranges in the "ranges" sheet

http://i.imgur.com/PRS5d.png

Basically after a combobox that will check that specific items are checked (i.e items 2 and 4 must be checked, doesn't matter if 1,2,4 or just 2 and 4, provided they're there).

[Vba]Private Sub cbInSys_Click()
ThisWorkbook.Worksheets("Note").Rows("9").Hidden = Not cbInSys
End Sub

Private Sub cbItem1_Click()
If Me.cbItem1 = True Then
ThisWorkbook.Worksheets("Note").Rows("5").Hidden = False
Me.cboItem1.Value = "1"
ThisWorkbook.Worksheets("Note").Range("C5").Value = "1"
Else
ThisWorkbook.Worksheets("Note").Rows("5").Hidden = True
ThisWorkbook.Worksheets("Note").Range("C5").Value = ""
End If
End Sub
Private Sub cbItem2_Click()
If Me.cbItem2 = True Then
ThisWorkbook.Worksheets("Note").Rows("6").Hidden = False
Me.cboItem2.Value = "1"
ThisWorkbook.Worksheets("Note").Range("C6").Value = "1"
Else
ThisWorkbook.Worksheets("Note").Rows("6").Hidden = True
ThisWorkbook.Worksheets("Note").Range("C6").Value = ""
End If
End Sub
Private Sub cbItem3_Click()
If Me.cbItem3 = True Then
ThisWorkbook.Worksheets("Note").Rows("7").Hidden = False
Me.cboItem3.Value = "1"
ThisWorkbook.Worksheets("Note").Range("C7").Value = "1"
Else
ThisWorkbook.Worksheets("Note").Rows("7").Hidden = True
ThisWorkbook.Worksheets("Note").Range("C7").Value = ""
End If
End Sub
Private Sub cbItem4_Click()
If Me.cbItem4 = True Then
ThisWorkbook.Worksheets("Note").Rows("8").Hidden = False
Me.cboItem4.Value = "1"
ThisWorkbook.Worksheets("Note").Range("C8").Value = "1"
Else
ThisWorkbook.Worksheets("Note").Rows("8").Hidden = True
ThisWorkbook.Worksheets("Note").Range("C8").Value = ""
End If
End Sub


Private Sub cboGivenTo_Change()
ThisWorkbook.Worksheets("Note").Range("C3").Value = Me.cboGivenTo.Value
End Sub

Private Sub cboItem1_Change()
ThisWorkbook.Worksheets("Note").Range("C5").Value = Me.cboItem1.Value
End Sub

Private Sub cboItem2_Change()
ThisWorkbook.Worksheets("Note").Range("C6").Value = Me.cboItem2.Value
End Sub

Private Sub cboItem3_Change()
ThisWorkbook.Worksheets("Note").Range("C7").Value = Me.cboItem3.Value
End Sub

Private Sub cboItem4_Change()
ThisWorkbook.Worksheets("Note").Range("C8").Value = Me.cboItem4.Value
End Sub

Private Sub cbPolice_Click()

If Me.cbPolice = True Then
ThisWorkbook.Worksheets("Note").Rows("24").Hidden = False
Me.cboPolice.Value = "1"
ThisWorkbook.Worksheets("Note").Range("C24").Value = "1"
Else
ThisWorkbook.Worksheets("Note").Rows("24").Hidden = True
ThisWorkbook.Worksheets("Note").Range("C24").Value = ""
End If
End Sub


Private Sub cmdClear_Click()
Dim ctlObject As Control
For Each ctlObject In Me.Controls
If TypeName(ctlObject) = "CheckBox" Then ctlObject.Value = False
If TypeName(ctlObject) = "ComboBox" Then ctlObject = ""
Next ctlObject

ThisWorkbook.Worksheets("Note").Rows("6:10").Hidden = True
ThisWorkbook.Worksheets("Note").Rows("4").Hidden = True
End Sub

Private Sub cmdCopy_Click()

ThisWorkbook.Worksheets("Note").Range("A2:C10").Select
Selection.Copy
End Sub


Private Sub lbReceived_Click()

End Sub

Private Sub UserForm_Initialize()

Me.tbDate = Format(Date, "dd mmm yyyy")
ThisWorkbook.Worksheets("Note").Rows("6:10").Hidden = True
ThisWorkbook.Worksheets("Note").Rows("4").Hidden = True

End Sub
[/vba]


The thing is, the actual xls has around 65 items, and an expected 22 categories (each of which 6-11 items). I really want to make it as simple as possible.

Right now I think I can do it with a new combo box assigned to a named range and if the cbo.Value = Not "None" then it iterates until it finds the respective type/array and it then shows what is missing from the expected items for that array/range.

The only problem is I don't know how to do this.

The code below is my attempt at determining what's hidden and what's not

[vba]

Public Sub criteriaCheck1()
If cboCriteria.Value = Not "None" Then
If cboCriteria.Value = "type a" Then
criteriaCheckTypeA
End If
End If

End Sub

Public Sub criteriaCheckTypeA()
Dim strMissingItems As String
If Range("A5,A7:A8").EntireRow.Hidden = True Then

End If
End Sub

Public Function myMsgBox(ByVal strMissingItems As String)
MsgBox "The Following items are missing:" & _
vbCrLf & strMissingItems
End Function[/vba]

It's incomplete and I really think I've hit a road block here.
The first sub is to check that a criteria is selected
The second is to check that all the rows are not hidden.. and if they are I wanted to iterate over those and concatenate them all into a string (separated by new lines chars) which would then be submitted to the third sub which would pop up saying what's missing.

The sub I've made to shorten the code and make it easier is:

[vba]Public Sub rowCheck(ByVal rowNum As String, ByVal boolStatus As Boolean, ByVal strCBName As String)
Dim cboLocation As String
cboLocation = "cbo" & Right(strCBName, 2)
ThisWorkbook.Worksheets("Note").Rows(rowNum).Hidden = Not boolStatus
If boolStatus = True Then
ThisWorkbook.Worksheets("Note").Range("C" & rowNum).Value = "1"
Me.cboLocation.Value = "1"
Else
ThisWorkbook.Worksheets("Note").Range("C" & rowNum).Value = ""
Me.cboLocation.Value = ""
End Sub[/vba]

Most of it is working, but I can't get the cboLocation bit to work -- It just spits out "Method or data member not found".