Working with a grid in user form

[chipsworld]

Hi all,
Need some guidance...

I have a user form that has a grid that represents 7 years worth of text fields...it also has two text fields which are start date and end date.
it is laid out like... y1jan, y1feb, y1mar, etc all the way through y7dec
y1, y2, y3 etc are all populated with the year based off of a "start" date.

What I am trying to do is fill in the start month based on that start year and then color that month and every month there after until it hits the end date from another field.
I can populate the years pretty simply using y1 = year(startdate) and then y2=y1+1 etc...

My question is...how can I use that date to find the appropriate text field which represents the start month and color it?
i.e.: start date 5/1/15 and color the text field named y1may based upon the month?

Any help on a starting point would be greatly appreciated.

Currently I am populating the y1, y2, y3, etc with the below. txtqmobsrt represents the startdate text field.

Private Sub txtqmobsrt_AfterUpdate()
Dim mnth

If txtqmobsrt.Value > "" Then
y1 = Year(txtqmobsrt)
y2 = y1 + 1
y3 = y2 + 1
y4 = y3 + 1
y5 = y4 + 1
y6 = y5 + 1
y7 = y6 + 1
mnth = Format(Month(txtqmobsrt), "mmm")
End If

End Sub

 

[chipsworld]

Norie...
sorry, just reread your post. An expanding label field would not work, as part of the calculation is to count the months of the qualifying period. If that period does not reach 12, the user then continues counting into the current MOB period to reach 12. It is at this point where the "person" the data is being generated for would achieve anything.
I apologize for being vague, but this is a Gov thing and classifications could apply.

Basically I am collecting date ranges. The first has a start and end date (only month and year matter) and an end range (only month and year matter). Based upon the length of the first range, there are rewards the person can achieve. I want to reflect those ranges visually for the user entering the date ranges... does that make sense?

 

[Norie]

So let's say you have all these labels/textboxes named y1Jan, Y2Feb etc.

How, from your calculations, to you determine which ones to highlight?

I can't see anything in your code that would help with that but I could be missing something.

P.S. I think the way I would approach this would be to have an array with 7 rows and 12 columns.

I would go through that array if a month/year has to be populated I would put True in the relevant position of the array.

Something like this which uses random values.

Dim arrYM(1 To 7, 1 To 12) As Boolean
Dim idxMonth As Long
Dim idxYear As Long

    For idxYear = 1 to 7
        For idxMonth = 1 to 12
            arrYM(idxYear, idxMonth) = Rnd()>0.5
        Next idxMonth
    Next idxYear

You could then use that array to highlight the labels/textboxes.

For idxYear = 1 to 7
        For idxMonth = 1 to 12
            If arrYM(idxYear, idxMonth) Then
                Me.Controls("y" & idxYear & LCase(MonthName(idxMonth, True)).BackColor = vbYellow
            End If
        Next idxMonth
    Next idxYear

 

[chipsworld]

Norie,
So, the values are being populated from text fields on the form. Basic DateDiff values returned to do the calculations.
That part of the code was provided here:

Version:1.0 StartHTML:000000242 EndHTML:000016218 StartFragment:000004003 EndFragment:000016051 StartSelection:000004003 EndSelection:000016051 SourceURL:https://www.mrexcel.com/board/threads/working-with-a-grid-in-user-form.1147654/ Working with a grid in user form | MrExcel Message Board
Sub CALCULATE_PDMRA()
Dim mnths As String, qmobsrt As String, qmobend As String, cmobsrt As String
Dim cmobend As String, pdmraern As String, qcomp As String, ccomp As String
Dim qusc As String, cusc As String, qcntry As String, ccntry As String, qmnths As String, cmnths As String, pmnths As String


Dim wspdmra As Worksheet

Set wspdmra = Sheets("PDMRA HISTORY")
qcntry = Me.cmbqcntry.Value 'country from qualifying MOB
ccntry = Me.cmbccntry.Value ' country from current MOB
qusc = Me.cmbqusc.Value ' qualifying MOB Order type
cusc = Me.cmbcusc.Value ' current MOB order type
qcomp = Me.cmbqcomp.Value ' qualifying component
ccomp = Me.cmbccomp.Value ' current component
qmobsrt = Me.txtqmobsrt.Value ' qualifying MOB start date
qmobend = Me.txtqmobend.Value ' qualifying MOB end date
cmobsrt = Me.txtcmobsrt.Value ' current MOB start date
cmobend = Me.txtcmobend.Value ' current MOB end date
pdmraern = Me.txtpdmraern.Value ' number of PDMRA days earned
mnths = DateDiff("m", qmobend, cmobsrt) ' number of months between QMOB end and Current MOB start
qmnths = DateDiff("m", qmobsrt, qmobend) + 1
cmnths = DateDiff("m", cmobsrt, cmobend) + 1
If mnths <= 72 Then 'And Year(qmobend) < Year(Date) Then
If ccntry = "Afghanistan" Or ccntry = "Iraq" Then
If qmnths >= 12 Then
pdmraern = cmnths * 2
pmnths = cmnths
ElseIf qmnths < 12 Then
pmnths = cmnths - (12 - qmnths)
pdmraern = pmnths * 2
End If

Else
If qmnths >= 12 Then
pdmraern = cmnths
pmnths = cmnths
ElseIf qmnths < 12 Then
pmnths = cmnths - (12 - qmnths)
pdmraern = pmnths
End If
End If
End If

Me.txtmonthsdwell.Value = mnths
Me.txtpdmraern.Value = pdmraern ' number of PDMRA days earned
Me.txtmnthsearned.Value = pmnths

Call FILL_IN


End Sub

The next sub is what is used to populate the year labels for each row:

Sub FILL_IN() 
Dim qmobsrt As String

qmobsrt = Me.txtqmobsrt.Value
 Me.y1.caption = Year(qmobsrt)
 Me.y2.caption = Year(qmobsrt) + 1 
Me.y3.caption = Year(qmobsrt) + 2 
Me.y4.caption = Year(qmobsrt) + 3 
Me.y5.caption = Year(qmobsrt) + 4 
Me.y6.caption = Year(qmobsrt) + 5 
Me.y7.caption = Year(qmobsrt) + 6 
End Sub

Pretty straight forward...

The part I can't figure out is how to take that and turn it into colored label fields on the form.

I like you idea of using an array, but I don't see how to bring that array in line with what I have.

I am gonna play with this and see what I can figure out...be ready for more questions! LOL Haven't really done a lot of work with Arrays in VBA, so kind of figuring this out as I go...

 

[chipsworld]

Norie,
I am getting a syntax error for the following code line...

Me.Controls("y" & idxYear & LCase(MonthName(idxMonth, True)).BackColor = vbYellow

Also...first question. You have declared the idxYear and idxMonth variables, but I can't figure out how to set them to anything...

The below is where I placed your code. This is where the labels for the years is set based upon the qmobsrt (beginning of mobilization and date entered into text field on user form). This is what I am trying to build from. How can I tell the code that y1 is equal to 2017 (random year), y2 = 2018, etc, etc? OR do I even need to? The array has to have a reference(?) and that is what I don't understand...how do I connect those two parts?

Sub FILL_IN()
Dim qmobsrt As String
Dim arrYM(1 To 7, 1 To 12) As Boolean
Dim idxMonth As Long
Dim idxYear As Long
qmobsrt = Me.txtqmobsrt.Value
Me.y1.caption = Year(qmobsrt)
Me.y2.caption = Year(qmobsrt) + 1
Me.y3.caption = Year(qmobsrt) + 2
Me.y4.caption = Year(qmobsrt) + 3
Me.y5.caption = Year(qmobsrt) + 4
Me.y6.caption = Year(qmobsrt) + 5
Me.y7.caption = Year(qmobsrt) + 6

For idxYear = 1 To 7
For idxMonth = 1 To 12
arrYM(idxYear, idxMonth) = Rnd() > 0.5
Next idxMonth
Next idxYear

For idxYear = 1 To 7
For idxMonth = 1 To 12
If arrYM(idxYear, idxMonth) Then
Me.Controls("y" & idxYear & LCase(MonthName(idxMonth, True)).BackColor = vbYellow
End If
Next idxMonth
    Next idxYear
End Sub

Thanks...

 

[chipsworld]

Norie,
OK...been racking my brain over this all weekend. Definitely need some guidance...

How do I take a date range based upon a start date, and end date (code in previous posts) and tie it to your array? That is the part I don't get...

Thanks

 

[JackDanIce]

Hey Chip, afraid I can't tell what's going on (where on the form are the years shown on your picture, if they are meant to?), however, taking @Norie's suggestion, on a spreadsheet can you try following:

A1: 10/15/2020
B1: =TEXT(A1,"YYYYMM")

And see if that inspires anything and ties back to what you need?

Something like this, be easier to decipher if you can post a link to a dummy file with the form and code

Alternatively, try a clean restart and break down to basics with examples, e.g.

Inputs
Start_Date 1/1/2018
End_Date 10/15/2020
Is there a minimum gap or maximum between the dates? Anything else to test specifically for the two input dates only?

Process
Forget Y1MMM, Y2MMM etc
Show with example, e.g.

Step1
Start_Date = 1/1/2018
this needs to change to "2018Jan"
Is this value a string or are you still treating it as a date value but in format YYYYMM? To be honest this is adding unnecessary complexity, I'd worry about formatting later or K.I.S.S - keep it simple (stupid)

Step2
Create a list of dates from Start_Date to End_Date on a daily/monthly/yearly gap basis
I.e. Do you mean 1/1/2018, 2/1/2018 ... 10/15/2020
Or 1/1/2018, 1/2/2018, 1/2/2018 ... 10/14/2020, 10/15/2020

Step3
Forget colours, pretend a colour is a number, make a map of dates to numbers e.g.
1/1/2018 maps to 1
2/1/2018 maps to 2
...
10/15/2020 maps to 34

Or is there a function that converts a date to a value? A map is like a VLOOKUP or VBA scripting dictionary. I'm sure you can see how this could be turned into RGB values for colour purposes.

Output
List of dates with specific numbers against them

Now you've got all your data processing and calcs worked out, separate step of hooking it into the form and display to user.

 

[chipsworld]

OK...as requested, here is a link to a clean version of the whole thing.

Shared - Google Drive

drive.google.com

The focus is on form frmPDMRAcalc.

As stated previously, I am trying to do nothing more than use a start date and end daate to highlight (color) a series of label fields to "show" a visual representation of the date range in months.

I look forward to any help I can get.

Also...please keep in mind that this has been a very long project that has constantly changed, so there is some pretty crazy code in there! Please do not laugh to hard!! LOL

Thanks!

 

[Norie]

Could you post an example of which 'cells' in the grid should be filled in based on the dates entered?

 

[chipsworld]

If you look at frmPDMRAcalc, in the lower right quadrant, there are 7 rows of 12 label fields. Then to the immediate left of those fields are 7 more label fields that will auto populate the years based upon the qmobsrt field on the form.

for instance... If entered into qmobsrt - 1/1/17 and then entered into qmobend - 1/1/18, the year fields (y1 thru y7) would populate with 2017, 2018, 2019, 2020, 2021, etc. With each year representing a series of 12 month fields to its right.

My goal is to use the start and end dates, and color the appropriate fields (months) with a color for the months starting with qmobsrt month and ending with the qmobend month and all months in-between.

My problem is not knowing how to take those dates and convert them into the series of months and use that to color the label fields on the form. Thereby providing a visual representation of the date period in months.

Hope that makes more sense...

 

[chipsworld]

OK...here is a pic of my ultimate goal...

IF you look at this pic, you can see two separate series of dates Qualifying MOB and Current MOB. I want to have the fields colored as shown in this picture...

Green for Qualifying MOB and Blue for Current MOB, thereby providing a visual representation of the two MOB periods.

As I indicated previously, the year labels on the left of the form are populated based upon the Qualifying MOB "MOB Start" and go from there.

ALL of my actual calculations are done. I get the data that I need, i am just trying to provide the visual for those date periods.

If you can show me how to do one, I can adapt it to the other.

Thanks!