Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DATES AND SPREADSHEETS (EXCEL)

    i have a workbook with 12 months of data within. The user would like to input his/her code through a user form.
    there are two dates they need to input Me.txtSCheduledDate.Value and Me.txtACTDATE.Value.

    the workbook contains 12 individual spreedsheets. i have tried data validation for each spreedsheet but my data goes to the last month (december). is there a way for me to use vb code to ensure that the correct dats go into the correct month?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: DATES AND SPREADSHEETS (EXCEL)

    Yes it could be done, but I am not sure exactly what your setup is and how the userform is done. Could you attach a sample workbook with the sheets and elaborate?

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DATES AND SPREADSHEETS (EXCEL)

    What are the names of thw worksheets?

    What code are you using now to enter the data into a worksheet?

  4. #4
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DATES AND SPREADSHEETS (EXCEL)

    Hi Hans

    I have tried everything that you have given me in the past, it works if i manually run the macro
    but it nothing has ever worked automatically when the first of the month comes around.

    the names of the spreedsheets are AUG2008, SEP2008, OCT2008, NOV2008 DEC2008.

    the code i have used is the following:

    [i]Very long piece of code moved to attachment by HansV</small>
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DATES AND SPREADSHEETS (EXCEL)

    If I run a simplified version of your macro it enters data in the sheet AUG2008, so I don't understand the problem.

  6. #6
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DATES AND SPREADSHEETS (EXCEL)

    yes the current month does, but try entering something for oct or december. it also puts it in august.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DATES AND SPREADSHEETS (EXCEL)

    Yes, because that's what your code says:

    Set ws = Worksheets(Format(Date, "MMMYYYY"))

    This will ALWAYS refer to the worksheet for the current month.

    Do you want to enter the data into the sheet corresponding to txtSCheduledDate or into the sheet corresponding to txtACTDATE?

  8. #8
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DATES AND SPREADSHEETS (EXCEL)

    <P ID="nt"><font size=-1>(No Text)</font>
    Attached Files Attached Files

  9. #9
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DATES AND SPREADSHEETS (EXCEL)

    yes, any time the user enters data it should go into the correct month. it does not matter if they entered it in july for oct or sept for dec. as long as the dates state the given month 11(nov) 12(dec) etc... then all the data they enter should go into that month.

    i have attached in zip format the whole project. please review and tell me what you think. I think very highly of your expertise. it would do me a world of good to get your thoughts on it.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: DATES AND SPREADSHEETS (EXCEL)

    then you must get the date, before you set the worksheet. Then set the worksheet to the scheduled date. You code always enters the information into the current month...

    Steve

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DATES AND SPREADSHEETS (EXCEL)

    You haven't answered my question in <post:=725,187>post 725,187</post:>...

  12. #12
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DATES AND SPREADSHEETS (EXCEL)

    ok i moved that set ws line of code to the bottom and it gives me an error on the very next line, so i commented out that line and again it gave me an error on the very next line. AWWWWW!!!!!

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DATES AND SPREADSHEETS (EXCEL)

    You still haven't answered my question. <img src=/S/igiveup.gif border=0 alt=igiveup width=31 height=23>

  14. #14
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DATES AND SPREADSHEETS (EXCEL)

    my apologies, need it to refer to txtSCheduledDate.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DATES AND SPREADSHEETS (EXCEL)

    Try changing the line

    Set ws = Worksheets(Format(Date, "MMMYYYY"))

    to

    Set ws = Worksheets(Format(Me.txtSCheduledDate, "MMMYYYY"))

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •