Date picker scrollbar month and year only

[EnviroGeek]

Using 2003, I have a form (frmMSW) users input data for monthly solid waste
tonnage by location. Since data is collected monthly, wanted the user to be
able to select Month/Year period covered from a calendar scrollbar. Is it
possible?

FYI - using date picker scrollbar now. Users complaining they don't
understand what "Period Covered" means when the entire month calendar pops up.

Thank you guys for all the support. I've learned much from reading other
problems/solutions.

 

[Dave Peterson]

I created a small userform with a commandbutton, two labels and a scrollbar.

This is the code behind that userform:

Option Explicit
Dim myStartDate As Date
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub ScrollBar1_Change()

Me.Label1.Caption _
= Format(DateSerial(Year(myStartDate), _
Month(myStartDate) + Me.ScrollBar1.Value - 1, _
1), "mmmm dd, yyyy")

Me.Label2.Caption = Me.ScrollBar1.Value

End Sub
Private Sub UserForm_Initialize()

myStartDate = DateSerial(2010, 1, 1)

With Me.ScrollBar1
.Min = 1
.Max = 120
.SmallChange = 1
.LargeChange = 12
.Value = DateDiff("m", myStartDate, Date) + 1
Me.Label2.Caption = .Value
End With

Me.Label2.Visible = True 'just for testing
End Sub

 

[EnviroGeek]

Hey Dave - I've copied your code but the scrollbar does not display Month and
Year. I get a blank scrollbar

I've read "A Pop-Up Calendar for Excel" at fontstuff.com to try to
understand what I'm doing a little better. I'd like to mimic the scrollbar
section of the calendar...just don't need the days portion of a date.

I'm assuming the Label1 will go under the area month would display and
Label2 would go under the area year would display (on the scrollbar)?

I'm dying here...this is one of the last features needed to complete the
project...

Thanks for helping.

 

[EnviroGeek]

Sorry for double posting - have most of it working

In Label1 get Month and Year (yay!) but year starts 1899. Would like to
have month and year start at one month prior to current month (so the user
would see January, 2010 if they are filling in the form in February 2010).

In Label2 get a number. Starts at number 1 and for each month increase,
counts up by 1. I'm not sure what label 2 represents - which makes me unsure
about needing label 2.

thoughts/solutions?

Thanks a bunch for your help, never thought I'd get so excited to see a
Month/Year show up on the screen

 

[Dave Peterson]

I think you changed the code I used.

I had a start date specified and you lost that.

Take a look at that suggested code--or post your existing code.

 

[Dave Peterson]

ps.
Change the start date to what month/year you want to see when the scroll bar is
to the far left.

And change that 120 (I think I used 120) to what you need. I went out 10 years
(120 months).

 

[EnviroGeek]

Ok - making progress **yay** Based on the current code, the following applies:

1. What I need: to see the prior month when the form opens
What I see: Label1 empty until user hits scroll button, don't want user to
have to hit scroll button unless they are reporting on an older month.

2. What I need: the first month shown should be prior month (if it is
February 2010, user should see January 2010). We report prior month data so
no need for user to see current month - can't report data until month is over.
What I see: if UP scroll - Label1 remains blank, if DOWN scroll - January
2010 shows **this is working as needed**

3. What I need: user should not be able to DOWN scroll past prior month -
we are reporting historic data and should never put in data for the current
or future month.
What I see: user can DOWN scroll through unlimited month/years.

4. What I need: user can UP scroll 1 calendar year back in time regardless
of current date - so in December 2010, user can still UP scroll to fill in
data for January 2009 (yes, I realize that is 2 years of data).
What I see: user can UP scroll only 1 month (December 2009).

5. Addl data - I got rid of Label2, please don't get mad - I couldn't figure
out what it was for.

I'm not trying to be overly picky, but since our reporting/data collection
is always for past events, I don't need to be able to go into the future. I
don't even need current month.

MY CURRENT CODE:

Option Explicit
Dim myStartDate As Date
Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub ScrollBar1_Change()
myStartDate = Date
Me.Label1.Caption = Format(DateSerial(Year(myStartDate),
Month(myStartDate) + Me.ScrollBar1.Value - 1, 0), "mmmm, yyyy")
End Sub

Private Sub frmCalendarMonthYr_Initialize()

With Me.ScrollBar1
.Min = 1
.Max = 1
.SmallChange = 1
.LargeChange = 12
.Value = DateDiff("m", Date, myStartDate)

End With

End Sub

Private Sub UserForm_Click()

End Sub

 

[Dave Peterson]

First, you can't change the names of these built-in procedures.

Private Sub frmCalendarMonthYr_Initialize()
should be:
Private Sub UserForm_Initialize()

The userform_initialize procedure didn't run when the form was loaded/shown
(since you didn't have one anymore). Putting that back will fix most of the
problems.

Second, the label2 control was to show what the value of the scrollbar was--it's
used for testing so you can see if the value of the scrollbar creates the
correct text in label1. Put it back and just make it invisible when you're done
testing. It won't hurt anything and it'll be useful when you're debugging the
next problem <bg>.

Third. I'm confused at the months you want to be able to choose from.

I _think_ you want to see a 12 month span. So using today's date, you'd want to
see Jan 2010 through Feb 2009.

With that label2 visible, you'll notice that the value of the scrollbar varies
from 1 to 12 while the dates change from Feb2009 to Jan2010.

If that's true...

Option Explicit
Dim myStartDate As Date
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub ScrollBar1_Change()

Me.Label1.Caption _
= Format(DateSerial(Year(myStartDate), _
Month(myStartDate) - (12 - Me.ScrollBar1.Value), _
1), "mmmm dd, yyyy")

Me.Label2.Caption = Me.ScrollBar1.Value

End Sub
Private Sub UserForm_Initialize()

myStartDate = DateSerial(Year(Date), Month(Date) - 1, 1)

With Me.ScrollBar1
.Min = 1
.Max = 12
.SmallChange = 1
.LargeChange = 3
.Value = 12

Me.Label2.Caption = .Value
End With

Me.Label2.Visible = True 'just for testing
End Sub

 

[EnviroGeek]

You are the Man! Works perfectly.

After more reading, I know I will have to include LinkedCell info for the
spreadsheet I am storing the data in.

In the meantime, how do I get this calendar to pop up in the form my users
are actually populating with data?

A million thanks for the expert guidance

 

[Dave Peterson]

I wouldn't use a linkedcell.

Instead, I'd use the "ok" button on the userform to populate the cell. Then if
the user hits cancel, you don't have to worry that you destroyed any existing
data.

 

[EnviroGeek]

Dave - thanks for your help. Hopefully you can walk me through (or suggest
online training) for the next steps:

Some background of project: Users will click on a link on company gateway,
master userform will open with list of other userforms to be selected for
data input. Based on the department the user is working for, they will
select appropriate userform and provide data for prior month (such as MSW -
municipal solid waste tonnage sent to landfill, broken down by major branch
locations). On my 'test' userform (named frmMSW), once the user fills in the
required data and clicks command button "Save Data and Close form", their
data is saved on the next blank row in a specific worksheet (named MSW
Input). I used information found on Contextures website to create the first
userform- great resource!

You gave me the directions for the date scrollbar displaying month/year
only. Thanks! Now I need to get it to show up in the frmMSW and get the
data to the correct worksheet.

1. How do I get the date scrollbar to show up on my userform where the user
inputs all data? Do I have to put a field on the frmMSW? Do I just put in a
label that asks "Period Covered?" and let the user click a command button
"Show Calendar" that initiates the scrollbar you created?

2. I'm assuming I will add code that sends the date info to the spreadsheet
like I did with the other data on the userform. Since all the data from the
form is inserted on the next blank row of the assigned worksheet, I'll use
similar code.

Once I am finished with this form (frmMSW), I will continue creating other
forms for other departments that are specific to their data - such as energy
usage by location, water usage by location, green purchasing by location...

The end goal is to allow multiple users go to one gateway to load data,
generate a report and the final product will be a "dashboard" the head boss
will click on that will give an overview of all the data on one screen. The
data that meets company goals - such as 50% reduction of solid waste sent to
landfill - will show up green and the locations that have not met that goal
will show up red.

FYI - we didn't make this a database because we are not allowed to have
databases on the company gateway (IT rules, not mine).

Hope this information helps...and thanks again for fixing the scrollbar
problem

 

[Dave Peterson]

#1. You can pass variables between the forms, but I've found the simplest way
is to use a public variable in a General module (not behind a worksheet, not
ThisWorkbook, not in the userform module):

Public myStr as string 'or pass the date and format where you need it.

Then you can modify that public variable in any procedure in that project. And
you can retrieve the value, too.

If the date is a requirement for all the input, then I'd ask once and use that
variable. If date is not a requirement for all the input, then I think I'd only
put it on the userforms that need it.

Or put a button on those userforms that pops up a very small userform with just
what you want (the scrollbar and label???). The use of the public variable
would depend on how many dates you need to keep track of (maybe???).

#2. If the number of forms that the users need is small (whatever that means),
maybe you could use a miltipage userform -- a tab for each worksheet/input and
maybe a special tab for all the common stuff you need.

If that sounds reasonable, you could even disable/hide the pages that they
shouldn't see.

Maybe a set of optionbuttons that show the pages you want to show.

=======
I'm not sure this helps or hurts...

I created a userform with a multipage control and 7 pages (Common and 6 "detail"
pages). It also had two commandbuttons. And on that common page, it had 6
optionbuttons.

This was the code behind that useform:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim iCtr As Long

'do a bunch of validity here

'if all is valid then unhide the page and go to it
For iCtr = 2 To Me.MultiPage1.Pages.Count
Me.MultiPage1.Pages(iCtr - 1).Visible _
= CBool(Me.Controls("Optionbutton" & iCtr - 1).Value = True)
If Me.Controls("Optionbutton" & iCtr - 1).Value = True Then
Me.MultiPage1.Value = iCtr - 1
End If
Next iCtr
End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long
Dim cCtr As Long
Dim myCaptions As Variant
Dim HowManySecondaryPages As Long

HowManySecondaryPages = Me.MultiPage1.Pages.Count - 1

myCaptions = Array("Dept A", _
"Dept B", _
"Dept C", _
"Dept D", _
"Dept E", _
"Dept F")

If (UBound(myCaptions) - LBound(myCaptions) + 1) _
<> HowManySecondaryPages Then
MsgBox "Design error!"
Exit Sub
End If

With Me.CommandButton1
.Caption = "Cancel"
.Cancel = True
End With

Me.MultiPage1.Pages(0).Caption = "Common"
cCtr = LBound(myCaptions)
For iCtr = 2 To Me.MultiPage1.Pages.Count
Me.MultiPage1.Pages(iCtr - 1).Visible = False
Me.MultiPage1.Pages(iCtr - 1).Caption = myCaptions(cCtr)
Me.Controls("Optionbutton" & iCtr - 1).Caption = myCaptions(cCtr)
cCtr = cCtr + 1
Next iCtr

End Sub

 

[EnviroGeek]

Current Status - boss standing over my shoulder with his hands on his hips
and tapping his foot *groan*

Was not able to get calendar to work properly, went back to my file
pre-calendar/ date issue. Inserted calendar control - Calendar 11 - on my
form and it works perfectly, only displays month and year!

Now - how do I get date from the calendar control on my form to go to my
worksheet?

Worksheet name is MSW Input
currently have form dumping data to next blank row, using code :

'copy date data submitted
ws.Cells(iRow, 1).Value = Me.tBoxTodayDate.Value

'copy period covered to the spreadsheet
ws.Cells(iRow, 2).Value = Me.Calendar1.Value

'copy the data to the spreadsheet
ws.Cells(iRow, 3).Value = Me.tBoxNBCMSWLandfill.Value
ws.Cells(iRow, 4).Value = Me.tBoxNBCMSWRecycle.Value
ws.Cells(iRow, 6).Value = Me.tBoxNBCCDLandfill.Value
ws.Cells(iRow, 7).Value = Me.tBoxNBCCDRecycle.Value
etc, etc - have 42 data collection fields

Everything works except the cell (iRow, column 2) where the calendar data is
supposed to go, it remains blank. Formatted column 2 as Date in format m, 01
for month year

I'm just now implementing the multiform suggestions. As always, thanks for
your help...more to come ~ Envirogeek

 

[Dave Peterson]

The code looks ok to me. I'd add a msgbox to see if the value in the calendar
control was what you expected. Maybe it hasn't been set yet????

 

[EnviroGeek]

Ok - had to change Calandar1 Value IS Null in the Properties menu from True
to False.

Date goes to worksheet but every date is January 2010 regardless of the
month/year selected in Calendar1. Noticed Value in Properties menu is filled
in with 1/2010. Shouldn't it be blank so the user selected value is
transferred to the next blank row?

Help, I'm almost finished

 

[Dave Peterson]

I'm not sure I understand.

How can the value of the calendar control be 1/2010. That's not a date.

Remember you could still use a label/textbox to show the value of the calendar
control. That gives the user positive feedback on what they chose.

And you can always check the value of the calendar before you try to put it in
the worksheet.

if isnull(me.calendar1.value) then
'don't do anything
else
'do all the work
end if

 

[EnviroGeek]

In VB, when I click on the calendar control for Period Covered (want month
year only - which is working), the properties window opens on the left side
of my screen. In Properties, the "Value" attribute is blank when the
"ValueIsNull" is set to TRUE - the date Period Covered will not transfer from
the completed userform (the column remains empty)

When I set the "ValueIsNull" to FALSE - the "Value" attribute property
immediately displays date 01/03/2010. I run the form, input data, calendar
control for Period Covered opens with January 2010 (I'm okay with this start
date). All my data goes to the worksheet appropriately except the date for
Period Covered (I have a Data Entry Date which is working appropriately and
captures todays' date as the date of data entry).

When I check all the data in the worksheet, Period Covered always fills with
1/3/2010 regardless of month/year I select from calendar control.

Not sure how to get the worksheet to capture the month/year the user inputs.

Thanks - EnviroGeek

 

[Dave Peterson]

I'm not sure what you're doing, but I put a calendar control and a commandbutton
on a small userform.

This worked fine:

Option Explicit
Private Sub CommandButton1_Click()
With Me.Calendar1
MsgBox .Value & vbLf & Month(.Value) & vbLf & Year(.Value)
End With
End Sub

If I clicked on a different date and then clicked the commandbutton, I'd see the
change.

Maybe you're changing months/years, but not selecting/clicking a day in that
month???

 

[EnviroGeek]

Thanks- I'm through with trying to make the form easy for the user. Everyone
will have to select a day even though we are only interested in collecting
the Month/Year for Period Covered. Will include a message that instructs all
users to always select the first day of the month/year they are reporting
data for (which will always be the month prior to current month).

It's frustrating that it is not easy to make a calendar control display and
collect data for month/year only.

Thanks for both your patience and expertise. You guys definitely help us
find our way through the Excel VB maze

 

[Dave Peterson]

You could just use two controls. A combobox for the month and another for the
year.