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

EnviroGeek wrote:

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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" wrote:

#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 wrote:

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" wrote:

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 wrote:

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" wrote:

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.

--

Dave Peterson