Date picker scrollbar month and year only

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.
0
Utf
2/24/2010 2:53:01 AM
excel.misc 78881 articles. 5 followers. Follow

19 Replies
1365 Views

Similar Articles

[PageSpeed] 46

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 wrote:
> 
> 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
0
Dave
2/24/2010 1:38:23 PM
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.

"Dave Peterson" wrote:

> 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 wrote:
> > 
> > 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
> .
> 
0
Utf
2/25/2010 10:09:02 PM
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 ;)


"EnviroGeek" wrote:

> 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.
0
Utf
2/25/2010 11:03:01 PM
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.

EnviroGeek wrote:
> 
> 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 ;)
> 
> "EnviroGeek" wrote:
> 
> > 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
0
Dave
2/25/2010 11:46:53 PM
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 wrote:
> 
> 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 ;)
> 
> "EnviroGeek" wrote:
> 
> > 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
0
Dave
2/25/2010 11:47:48 PM
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" wrote:

> 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 wrote:
> > 
> > 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 ;)
> > 
> > "EnviroGeek" wrote:
> > 
> > > 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
> .
> 
0
Utf
2/26/2010 1:06:01 AM
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 wrote:
> 
> 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" wrote:
> 
> > 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 wrote:
> > >
> > > 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 ;)
> > >
> > > "EnviroGeek" wrote:
> > >
> > > > 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
> > .
> >

-- 

Dave Peterson
0
Dave
2/26/2010 2:51:34 AM
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.
> 
> 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 wrote:
> > 
> > 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" wrote:
> > 
> > > 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 wrote:
> > > >
> > > > 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 ;)
> > > >
> > > > "EnviroGeek" wrote:
> > > >
> > > > > 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
> > > .
> > >
> 
> -- 
> 
> Dave Peterson
> .
> 
0
Utf
2/26/2010 11:26:01 PM
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.
> >
> > 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 wrote:
> > >
> > > 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" wrote:
> > >
> > > > 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 wrote:
> > > > >
> > > > > 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 ;)
> > > > >
> > > > > "EnviroGeek" wrote:
> > > > >
> > > > > > 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
> > > > .
> > > >
> >
> > --
> >
> > Dave Peterson
> > .
> >

-- 

Dave Peterson
0
Dave
2/27/2010 1:44:54 AM
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.
> > >
> > > 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 wrote:
> > > >
> > > > 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" wrote:
> > > >
> > > > > 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 wrote:
> > > > > >
> > > > > > 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 ;)
> > > > > >
> > > > > > "EnviroGeek" wrote:
> > > > > >
> > > > > > > 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
> > > > > .
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > > .
> > >
> 
> -- 
> 
> Dave Peterson
> .
> 
0
Utf
3/1/2010 11:59:01 PM
#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.
> > > >
> > > > 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 wrote:
> > > > >
> > > > > 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" wrote:
> > > > >
> > > > > > 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 wrote:
> > > > > > >
> > > > > > > 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 ;)
> > > > > > >
> > > > > > > "EnviroGeek" wrote:
> > > > > > >
> > > > > > > > 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
> > > > > > .
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > > .
> > > >
> >
> > --
> >
> > Dave Peterson
> > .
> >

-- 

Dave Peterson
0
Dave
3/2/2010 1:02:45 AM
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.
> > > > >
> > > > > 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 wrote:
> > > > > >
> > > > > > 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" wrote:
> > > > > >
> > > > > > > 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 wrote:
> > > > > > > >
> > > > > > > > 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
0
Utf
3/3/2010 10:23:02 PM
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.
> > > > > >
> > > > > > 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 wrote:
> > > > > > >
> > > > > > > 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" wrote:
> > > > > > >
> > > > > > > > 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 wrote:
> > > > > > > > >
> > > > > > > > > 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

-- 

Dave Peterson
0
Dave
3/3/2010 11:02:32 PM
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.
> > > > > > >
> > > > > > > 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 wrote:
> > > > > > > >
> > > > > > > > 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
0
Utf
3/4/2010 12:09:01 AM
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.
> > > > > > > >
> > > > > > > > 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 wrote:
> > > > > > > > >
> > > > > > > > > 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

-- 

Dave Peterson
0
Dave
3/4/2010 12:33:37 AM
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.
> > > > > > > > >
> > > > > > > > > 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 wrote:
> > > > > > > > > >
> > > > > > > > > > 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
0
Utf
3/4/2010 6:04:01 PM
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.
> > > > > > > > > >
> > > > > > > > > > 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 wrote:
> > > > > > > > > > >
> > > > > > > > > > > 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

-- 

Dave Peterson
0
Dave
3/4/2010 6:19:07 PM
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.
0
Utf
3/4/2010 7:24:02 PM
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
0
Dave
3/4/2010 7:59:59 PM
Reply:

Similar Artilces:

Querying Dates
Hello, I have a query with information in it and then one of the fields is a date field. I would like to further query it so that it only takes the information for the people that are before a certain date. Everything after that date I dont want. For ex: name address date Jane Doe Whatever St. 12/3/08 Doe Jane Who Knows St 12/15/09 I would like to quere out the people that have a date before 12/10/09. I tried when I do my query to just put under the date field <12/10/09, but that didnt work for some reason. It's seems sim...

Sliding dates on a graph?
I have created stacked column graphs using the OFFSET function to automatically update when each week's information is added to the worksheet. However, now my boss want's only the last 4-6 weeks of information to be displayed on the graph. Is there a way to do this without manually changing each data point (there are 6 for each week for each chart--YIKES) every week? Thank you for your time, Dee I found it! http://www.peltiertech.com/Excel/Charts/DynamicLast12.html "Dee Sperling" <sperling@infimed.com> wrote in message news:uE$hZxiLFHA.3064@TK2MSFTNGP12.phx.gbl....

Grouping dates by month and year
I have a spreadsheet with a range of dates in one column, and a list of statuses in the next (basically it is a list of orders, which are either open or closed). I want to create a list of open orders broken down by month. i.e. Some kind of array formula which goes down A1:A200 looks for any from say March 2010, then looks to B1:B200 for those marked as =91Open=92, and returns a count. It=92s been an age since I=92ve taxed my brain with this kind of stuff (been out of work) and I=92m sure this is probably quite easy. However, I=92ve falling at the first hurdle, trying to find a function to ...

Due date VS processing date in Money '06
This is really bothering me. In my previous version, when I entered a due date for an epay, my register showed that date. It didn't remind me that I need to have XXX dollars in my account on due date - 4 and then change the date in my register to due date - 4. I want my register to show the due date I entered. I have enough sense to know I need money in my account. How can I change this? The check box about using processing date instead of entered date is NOT checked. Did my upgrade magically switch me to a bill payment service that is actually drafting my account early and pu...

need a date look here
This is a multi-part message in MIME format --=_NextPart_2rfkindysadvnqw3nerasdf Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Get all the hots girls @ www.pearlymae.com --=_NextPart_2rfkindysadvnqw3nerasdf Content-Type: text/html Content-Transfer-Encoding: quoted-printable <html> <head> <meta name=3D"GENERATOR" content=3D"Microsoft FrontPage 6.0"> <meta name=3D"ProgId" content=3D"FrontPage.Editor.Document"> <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dwindow...

Reporting only current date forward
Hi everyone, I have a report in which I only want it to show the events that are either today or in the future and not show any of past events. Can you help me out with this one. I just got this db already created piecemeal and trying to sort thru everything. Thanks Lily "How" depends on "what" ... and you haven't described either the data structure you are using or what you've already tried. Have you looked into use a query and the selection criterion (untested): >=Date() Good luck! -- Regards Jeff Boyce Microsoft Acces...

Text import
Hi I have a text file which I import into Access table using transfertext method. everything is okay except few date columns are in ddmmyyyy format In some of the columns I have a date in the follwoing format ddmmmyyyy (i.e 13Oct2007) - without any slash or hyphen. How can I get this in proper data format 10/13/2008 (mm/dd/yyyy) either at the time of importing or after importing get this proper date in a different column. Aslo, I have some dollar amount columns and signage (negative or positve) is given in a separate column. So all the dollar amounts are abosolute value. Based on the...

Problem formatting date values in chart...
I have a blood pressure tracking spreadsheet -- at a minimum, it tracks Systolic and Diastolic pressure for a given day. A B C E.g.: Date Systolic Diastolic 1 9/3/04 120 90 2 9/4/04 123 87 3 9/5/04 129 79 I currently have a simple line graph that shows systolic data points over diastolic data points. The left hand (Y axis) value is "Pressure". My problem is that I want the "date" values of Col. A to display at the bottom of the graph as a label with each corresponding data point and...

Calendar To Stay at Last Date Entered
I have a calendar in a form. We do data entry for information that could have occurred today, yesterday, or a couple of days ago, like over a weekend. The form always goes back to todays date. How do I get the calendar to stay at the last date entered? Somehow you will need to record the last date entered in a table somewhere. I use a system table for such items. Bill wrote: >I have a calendar in a form. We do data entry for information that could >have occurred today, yesterday, or a couple of days ago, like over a weekend. > The form always goes back to todays date. How ...

Dates getting corrupted in Microsoft Money 2005
Does anyone else have this issue? Money is corrupting the transaction dates in my data. Account balances reconcile and all the transactions are there, but the dates of the transactions are getting progressively corrupted for some reason. Especially problematic as I'm trying to do my tak returns at the moment and nothing reconciles to the expected totals. I am on MM 2005. My data goes back to 1995 and my Money file is around 22MB in size. * Is there a later version I should try? * Should I re-install? * Can I recover or correct this data without going back to old archives (fortun...

How conditional format line with date of today #2
Hi, tried setting conditional where cell = today(). However, today was converted to ="today()" and the format was no applied. The format does work if I hardcode the date. I.e. it work if I pu =04/11/04 instead of =today(). However I need to use the today() function. Any other suggestions? Thanks -- dewsbur ----------------------------------------------------------------------- dewsbury's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1608 View this thread: http://www.excelforum.com/showthread.php?threadid=27535 Select the cell to CF. Format>...

Placing a Date Range on a Report
I have several reports that have been created in Microsoft Access that require a date range in order to pull the requested data. The date range was set up in the query that was built to run the report. Is there a way to get that date range displayed on the report? ...

start new month
how do i delete previous months entries to start a new month in my bank statement? or do i just keep adding???? In microsoft.public.money, craig wrote: >how do i delete previous months entries to start a new >month in my bank statement? or do i just keep adding???? Just keep adding. ...

HOT Reqs : Microsoft Business Solutions | 12+ Months | Chicago, IL (Functional and technical)
HOT Reqs : Microsoft Business Solutions | 12+ Months | Chicago, IL (Functional and technical) ---------------------------------------------------------------------------------------------------------------------------------------------- Microsoft Business Solutions (Microsoft Small Business Financials) Need one resource each for Functional and Technical roles. Location : Chicago, IL Duration : 12+ Months Funtional : This person will be responsible for the requirements gathering and setting up the system. Technical : This person will be responsible for installing and maintaining th...

Can't delete email in Inbox older than 1 year
I realize this sounds wierd. At work, I am using Outlook 2000 SR-1 which uses an Exchange server. Anyway, I have had these old emails i my Inbox that I can't delete. I was testing this and noticed that i the email was older than 1 year, I get the "Messaging Interface ha returned an unknown error....." when I try move or delete these ol emails. I can delete anything newer than 1 year. Very odd. I have cleaned up my pst file so that it is down to 190MB. I also ra the repair tool a few times, but it did not fix the problem. I have nice clean Inbox except for these 100 emails tha...

Sort Dates by Month and Day, Not Year
Is there a trick to sort a date column by Month and Day, and not have it sort the list by year as first sort. Without breaking up the data into multiple columns. Hi, You could add a column with the formula =YEARFRAC(DATE(YEAR(A1),1,1),A1) and sort on that column. It does sound like you want to avoid this. If your dates are the row field of a pivot table and your group by month and day Excel will ignore the year. If this helps, please click the Yes button. Cheers, Shane Devenshire "Al Franz" <albert@nospam.netmation.com> wrote in message news:OsrEqlJaJHA.5340@TK...

consolidating data by date
Sheet 1 is for Store 1. In column A I have dates, in column B I have profits. Sheet 2 is for Store 2 with the same data as Sheet 1. However, there is not necessarily an entry on every date. For example, Store 1 could have entries on 1/1, 1/2, 1/5. Store 2 could have entries on 1/1, 1/3, 1/5. How can I consolidate the data from these two sheets into one to look like this: 1/1 Store A profit Store B profit 1/2 Store A profit 1/3 Store B profit 1/5 Store A profit Store B profit So basically, I would want to consolidate the dates in colum...

Using Between...And with General Date
I am trying to query all records created between given dates. The field, DtRecCrt, is a general date field that is set to =Now() whenever the records are created. However, when I run the query and have the criteria for DtRecCrt set to "between [start date] and [end date]," it will return records up to but not including the end date specified. How do I make the query include records created on the end date? Thanks On Fri, 30 Apr 2010 13:08:04 -0700, mewins wrote: > I am trying to query all records created between given dates. The field, > DtRecCrt, is a gen...

Querying last months data
Hi All, I have created a query where I can get a count of a specific person's activity. These people log when they recieve mail and when they have actioned it. The problem that I have is these stats are gathered for the previous month i.e. in January we are looking at Decembers data. I can get the query to get the data from February to December but come January I cant go back to December. Here is the SQL code: SELECT Count([Experience Rating Table].DateCompleted) AS CountOfDateCompleted FROM [MyTable] GROUP BY [MyTable].AssignedTo, Month([DateCompleted]), Year([DateCompleted...

Choosing a field's record based on date
Hi. I am newish to Access and have a question. My friends familiar with Access don't know the answer to this one either. I feel like it is just on the tip of my brain but I can't get it. How can I select an item in a table based on the date? For example, an employee may have many hourly wages listed by date of hire and subsequent raises. How do I pick the right wage based on a given date? (Looking back at labor reports, I want to solve this question: On this day the employee worked 10.5 hours. What did he earn?) I have an employee list table and a linked wages table that show...

MS Access 2007 Query Separating Info into months columns
Hello, I have a contact table (Tblcontacts) that has fields (FldID[PrimaryKey, autonumber], FldFirstName, FldLastName, FldPhone, FldStreet, FldCity, FldState, FldZipcode, Fld0to5, Fld6to18, Fld19to40, Fld41to60, Fld60plus, FldComments). 2nd Table is TblVisits. Fields are (FldID2[Primarykey, autonumber], FldVisit[Date], FldID[ForeignKey][number],FldVisComments) The fields with numbers receive a number by the number of Family members in that age catergory. (Fld0to5 with a value of 3 means 3 family members are in that age bracket. The cotact info is for the head of household...

How to find the inerest pay in 3 years
Hi all I would like to know wich formula to use to calculate the interest pai in a 3 years loan Principal : 16808.10 Interest rate :2.75% term :3 years I would like to get the "Cost to borrow" in just one step formula I can calculate the monthly payment , multiply this for 36 months an take away the principal, but I think there might be a way to do thi directly. Thanks a lot for your time and attention at this matte -- Message posted from http://www.ExcelForum.com Use the Analysis Toolpak Addin function CUMIPMT(). In article <3dsets.18s...

date in wrong format
My mo/da/yr format somehow changes to a 5 digit entry - 393xx. I have tied the normal procedure of format cells, date, etc. but that won't fix it. What can I do? Mike this is a little long winded but I found it helpful in resolving many a format problem: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q264372 "Mike" wrote: > My mo/da/yr format somehow changes to a 5 digit entry - 393xx. I have tied > the normal procedure of format cells, date, etc. but that won't fix it. What > can I do? You should also be able to type 1 into a cell, copy it an...

Counting checkboxes between two dates
I'm tring to build a query that will count the number of checkboxes between the dates of two other fields. I have two tables within the query. One table (tblInspection) contains InspectionID, InspectionDate, and Citation, among others. The other table (tblReinspection) contains ReinspectionID, and ReinspectionDate, among others. There is a one to many relationship between the two tables, tblInspection being the parent table. Only one InspectionDate will be entered but none or many ReinspectionDates may be entered. The Citation field is a checkbox. What I am tring to do is to find all the ...

Need help with a project sheet, need date to show/not show based o
Hello, I have the following formula in cell S2 =IF(B2="","",DATE(YEAR(B2),MONTH(B2),DAY(B2)+31)) This takes a date in cell B2 and adds 31 days to it and displays it. What I need to do is to NOT have cell S2 show any date if anything is in cell D2. I'm not very adept at Vis Basic if that what it will take to do it. Thanks Max Hi, No need for code =IF(OR(B2="",D2<>""),"",DATE(YEAR(B2),MONTH(B2),DAY(B2)+31)) Mike "Max" wrote: > Hello, > > I have the following formula in cell ...