UserForm Offset Find Heading Match

[healey21]

I have been asked to create a form where users can select a drop down (Combo Box) and then it will search along a row to find a match, once found it will then look down the column until it finds entries with either a value of 4 or 5 and then colour the interior of the cell Green.

I have been able to create some of the code but have got stuck on how to find the headings. Here is a sample of the form and the code.

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 98px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Team Member</TD><TD>Maths</TD><TD>English</TD><TD>French</TD><TD>German</TD><TD>Art</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>John</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Sarah</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Anne</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>Bill</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>Gary</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Initialize the form to fill the combo box

Private Sub UserForm_Initialize()
Sheets("Sheet1").Activate
Range("A4").Select
With Me.cboSubject
.AddItem "Maths"
.AddItem "English"
.AddItem "French"
.AddItem "German"
.AddItem "Art"

End With
End Sub

The OK button to find the match and then colour, To match the heading in cboSubject

Private Sub cmdOK_Click()
'How can I add the code to find the heading rather than refer to column 4
Dim i As Integer
i = 4
Application.ScreenUpdating = False
ActiveCell.Offset(0, i).Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value >= 4 Then 'How do I get to look for 4 and 5, there will be greater values than 6
ActiveCell.Interior.Color = vbGreen
End If
ActiveCell.Offset(1, 0).Select
Loop
Range("a4").Select
Application.ScreenUpdating = True

End Sub

 

[Norie]

So is it one combobox for group and one for header/title/subjects/whatever in that group?

eg you pick Transport from the first combobox and the 2nd combobox get's populated with Trains, Boats etc

Or is it a combobox per group?

Either could be done but the former would probably be easiest, with the latter you could end up with a pretty messy userform.

 

[healey21]

Unfortunately they will require a number of selections, so as you say the latter will be a little messy, but I think that would be there preference as they will see each Group straight away.

What I thought I would do to try and make it easier to handle is make a second sheet with the group names and list the items for each combo box to fill, they now have a column gap between each and I have managed to create the form with the 10 drop downs and choices in them, should they want to change or add them I thought it might be easier this way.

So my second sheet (Named Topics) now fill the combos and the first sheet is where the main data goes, which need the colour options if 4 or 5. If Ok I have copied the Topic Sheet layout and also the code for loading the Combos when the form initializes. I am still learning VBA but gettign through some extra tasks, mostly thanks to the Forum.

Topics

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 101px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BACKGROUND-COLOR: #00ccff">Subjects</TD><TD> </TD><TD style="BACKGROUND-COLOR: #ffffcc">Transport</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="BACKGROUND-COLOR: #ffffcc">Maths</TD><TD> </TD><TD style="BACKGROUND-COLOR: #ffffcc">Trains</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="BACKGROUND-COLOR: #ffffcc">English</TD><TD> </TD><TD style="BACKGROUND-COLOR: #ffffcc">Boats</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="BACKGROUND-COLOR: #ffffcc">French</TD><TD> </TD><TD style="BACKGROUND-COLOR: #ffffcc">Cars</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="BACKGROUND-COLOR: #ffffcc">German</TD><TD> </TD><TD style="BACKGROUND-COLOR: #ffffcc">Buses</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="BACKGROUND-COLOR: #ffffcc">Art</TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Code

Private Sub UserForm_Initialize()
Application.ScreenUpdating = False
Sheets("Topics").Select
Range("A2").Select
Do Until ActiveCell.Value = ""
'Subjects
Me.cbo1.AddItem ActiveCell.Value '1
ActiveCell.Offset(1, 0).Select
Loop
Range("C2").Select
Do Until ActiveCell.Value = ""
'Transport
Me.cbo2.AddItem ActiveCell.Value '2
ActiveCell.Offset(1, 0).Select
Loop
Sheets("Sheet1").Select
Application.ScreenUpdating = False

End Sub