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

 

[Norie]

Which year do the y1 textboxes represent?

 

[chipsworld]

Norie...the y1 is based on the year of the txtqmobsrt date field.
Here is a pic of the form. That may help in understanding what I am talking about.
txtqmobsrt is from the "Quallifying MOB" MOB Start field.

 

[chipsworld]

BTW: This is in Excel 2016 if that helps at all. Sheet could be run in various Excel versions however, so trying to make it as universal as possible.

 

[Norie]

Does this need to be done using a userform?

Couldn't you user a worksheet as the 'form'?

 

[chipsworld]

Norie,
A userform is so that there is a visual for the information. If its a HUGE problem, I can skip it.
I can create the calculations without the grid portion.

This is all part of a much larger setup that collects and computes several things from user input data. This is just one portion of the whole.
Thanks though...

 

[Norie]

I'm not saying it's a problem, but it is kind of difficult to figure out things without more information.

For one thing, are you putting values in the textboxes or are you only using them, via formatting, for a visual effect?

If it's the latter I would suggest changing the textboxes to labels.

If it's the former, where would the values for the textboxes be coming from?

 

[chipsworld]

Norie,
Yes...I am only using it as a visual and just coloring them red or something.
As to the label idea, I can do that.

I have all of the actual calculations worked out, but would like to be able to give the end user a visual representation of what date ranges they are looking at.
There are actually two separate date ranges...this "Qualifying MOB date range" and the "Current MOB Date range" in a different color.
Only asking for one as the process would be the same for the other and I can figure that out.
I have never done anything like this before, and honestly have no idea where to even start.

 

[Norie]

Where are the results of the calculations coming from?

Have you considered, as I previously suggested, using labels but rather than replacing each textbox with a label have a label for each year and adjust the width/formatting based on the calculations?

P.S. If you could post some sample data and a mock up of what you are looking for I could post some code.

 

[chipsworld]

Norie,
Had this same thought...
Converting to labels instead of text fields is fine. I can do that. You already have a shot of the form above, but here is ALL of the code being used to generate the calculations.

Hope this helps...

THe basic action is to color in the month squares for each month included in the date range from start to finish for both qualifying and current MOB (Mobilization). THe idea is this allows the user to "SEE" it in a way that is like a visual verification before they commit.

All of these fields are also captured in a spreadsheet for historical records, but not until after they click the "Close" button. Closing the form and moving the generated data to another field which contains many other data points for the final generated output.

Sub CALCULATE_ELIGIBILITY()
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
Dim wspdmra As Worksheet

Set wspdmra = Sheets("PDMRA HISTORY")
qcntry = Me.cmbqcntry.Value
ccntry = Me.cmbccntry.Value
qusc = Me.cmbqusc.Value
cusc = Me.cmbcusc.Value
qcomp = Me.cmbqcomp.Value
ccomp = Me.cmbccomp.Value
qmobsrt = Me.txtqmobsrt.Value
qmobend = Me.txtqmobend.Value
cmobsrt = Me.txtcmobsrt.Value
cmobend = Me.txtcmobend.Value
pdmraern = Me.txtpdmraern.Value
If qmobend > "" Then
mnths = DateDiff("m", qmobend, cmobsrt)
Else
Exit Sub
End If


If Year(qmobsrt) < (Year(cmobsrt) - 6) Then
MsgBox "MOB from qualifying DD-214 does not fall within the 72 Month limit!", vbOKOnly, "PDMRA NOTIFICATION"
ElseIf mnths > 72 Then
MsgBox "SM Does not qualify for PDMRA", vbOKOnly, "PDMRA NOTIFICATION"
Else


Call CALCULATE_PDMRA

End If

End Sub

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

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