More VBA code help... please!!!

[kidwispa]

Hi All,

Carrying on from my previous threads where I have asked for help populating a number of textboxes based on the results of 1 or 2 comboboxes, I am now in need of assistance with the following form:

I've deleted out personal details but to clarify:

In each frame there are between 4 and 7 textboxes on the left hand side containing the names of the various agents working for my company. What I am trying to achieve is when the combobox at the top of the form is populated and the "Retrieve" button is clicked, the correct results are populated from my main data sheet (entitled "QtrData").

Here is the original form I created and got to work (with some of your help)...

and here is the code used for this sheet (i've deleted out some that relates to conditional formatting as not relevant for this)..

Private Sub CommandButton7_Click()
Dim criteria As String
Dim rng As Range
Dim result
 
criteria = ComboBox2.Text
If Trim(criteria) <> "" Then
result = Application.Match(criteria, Worksheets("QtrData").Range("B:B"), 0)
If Not IsError(result) Then
With Worksheets("QtrData")
 
TextBox53.Text = .Range("C:C")(result).Text
TextBox52.Text = .Range("D:D")(result).Text
TextBox51.Text = .Range("G:G")(result).Text
TextBox47.Text = .Range("K:K")(result).Text
TextBox43.Text = .Range("P:P")(result).Text
TextBox39.Text = .Range("T:T")(result).Text
TextBox35.Text = .Range("Z:Z")(result).Text
TextBox31.Text = .Range("AC:AC")(result).Text
TextBox50.Text = .Range("AF:AF")(result).Text 
TextBox46.Text = .Range("AJ:AJ")(result).Text
TextBox42.Text = .Range("AO:AO")(result).Text
TextBox38.Text = .Range("AS:AS")(result).Text   
TextBox34.Text = .Range("AY:AY")(result).Text   
TextBox30.Text = .Range("BB:BB")(result).Text   
TextBox49.Text = .Range("BE:BE")(result).Text    
TextBox45.Text = .Range("BI:BI")(result).Text   
TextBox41.Text = .Range("BN:BN")(result).Text 
TextBox33.Text = .Range("BX:BX")(result).Text
TextBox29.Text = .Range("CA:CA")(result).Text
TextBox48.Text = .Range("CD:CD")(result).Text
TextBox44.Text = .Range("CH:CH")(result).Text
TextBox40.Text = .Range("CM:CM")(result).Text
TextBox36.Text = .Range("CQ:CQ")(result).Text
TextBox32.Text = .Range("CW:CW")(result).Text
TextBox28.Text = .Range("CZ:CZ")(result).Text
TextBox27.Text = .Range("DA:DA")(result).Text
TextBox54.Value = .Range("DB:DB")(result).Text
 
End With
Else
MsgBox "Unable to find reference"
End If
Else
MsgBox "Unable to find reference"
End If
 
End Sub

However this only shows the results for 1 agent. The difficulty I have is that I need to match many text boxes with their corresponding results for the correct quarter given in the combobox and I'm not sure where to begin!

Can anyone help? I hope I've given you enough information but let me know if there's anything else you need to know...

Thanks

 

[kidwispa]

Hi All,

I've managed to find a (fairly longwinded) way of achieving the results I require like this...

Private Sub CommandButton5_Click()
Dim criteria1 As String
Dim rng As Range
Dim result
criteria1 = ComboBox2.Text
 
If criteria1 = "1" Then
If TextBox11.Text = "Name1" Then
TextBox20.Text = Worksheets("QtrData").Range("G7").Text
TextBox27.Text = Worksheets("QtrData").Range("K7").Text
TextBox31.Text = Worksheets("QtrData").Range("P7").Text
TextBox35.Text = Worksheets("QtrData").Range("T7").Text
TextBox39.Text = Worksheets("QtrData").Range("Z7").Text
TextBox43.Text = Worksheets("QtrData").Range("AC7").Text
End If
If TextBox10.Text = "Name2" Then
TextBox21.Text = Worksheets("QtrData").Range("G14").Text
TextBox26.Text = Worksheets("QtrData").Range("K14").Text
TextBox30.Text = Worksheets("QtrData").Range("P14").Text
TextBox34.Text = Worksheets("QtrData").Range("T14").Text
TextBox38.Text = Worksheets("QtrData").Range("Z14").Text
TextBox42.Text = Worksheets("QtrData").Range("AC14").Text
End If
If TextBox9.Text = "Name3" Then
TextBox22.Text = Worksheets("QtrData").Range("G8").Text
TextBox25.Text = Worksheets("QtrData").Range("K8").Text
TextBox29.Text = Worksheets("QtrData").Range("P8").Text
TextBox33.Text = Worksheets("QtrData").Range("T8").Text
TextBox37.Text = Worksheets("QtrData").Range("Z8").Text
TextBox41.Text = Worksheets("QtrData").Range("AC8").Text
End If...

Which is fine, however with 15 different agents, 3 subtotals and a grandtotal being populated for 4 different quarters and total year the code is 843 lines long!

This in itself isn't a problem (as I'm working in xl2010 it works rapidly and the file size isn't that big), but what I want to do is apply conditional formatting to each of the 115 textboxes based on the results being shown at the time. I have tried adding in the following code for each text box for each quarter...

If TextBox51.Text <> "-" Then
If TextBox51.Text <> "" Then
If TextBox51.Text <= 1 Then TextBox51.BackColor = RGB(0, 255, 0)
If TextBox51.Text <= 1 Then TextBox51.ForeColor = RGB(0, 0, 0)
If TextBox51.Text > 1 Then TextBox51.BackColor = RGB(255, 0, 0)
If TextBox51.Text > 1 Then TextBox51.ForeColor = RGB(0, 0, 0)
End If
End If
If TextBox51.Text = "-" Or TextBox51.Text = "" Then TextBox51.BackColor = RGB(128, 128, 128)
If TextBox51.Text = "-" Or TextBox51.Text = "" Then TextBox51.ForeColor = RGB(0, 0, 0)

However this makes the code too long for excel to handle. Can someone please help me so I can add this code for each cell, possibly in a separate module, to run concurrently with the code I've written?

 

[kidwispa]

Bump

 

[shg]

I realize you're pretty immersed in your form at this point, but it looks to me like you could do this a whole lot easier with just formulas on a worksheet. Maybe you need a calculation sheet to feed a presentation sheet, or maybe you could calculate directly in the presentation cells, but either way, you could skip a thousand lines of code.

 

[kidwispa]

Thanks for the reply shg - I think it would probably be much easier to use the option you suggest, but as you rightly pointed out I am too far into the project to change it now (with this being due for completion by the middle of next week )

Prior to starting in my current job at the beginning of March I had never used VBA before, so I've found the whole process of this project extremely useful, and the help received on this forum invaluable - it's great that there are people out there with such a high level of expertise in excel/vba that are prepared to help people like me learn new tricks...



EDIT - Should have mentioned that I've now decided to only use conditional formatting on the grand total cells at the bottom of the page so have successfully managed to accomplish this now