Results 1 to 10 of 10
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need help correcting error's in code (excel/vba 2003)

    About question 1:
    I have told you several times now how to use the <!t>
    Code:
     and <!t>
    tags, and I have told you several times that you should first check whether the values are correct, and only then insert values into the worksheet. Telling you the same thing again seems useless. Please review the replies that you have received to your previous questions. and read them carefully.

    About question 2:
    In what sense did the workbook fail? Details, please.

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

    Need help correcting error's in code (excel/vba 2003)

    ok here is my code, if anybody can help improve on this code please assist.

    right now my problems lie with the following:

    1). I need to check to ensure all the data inputted into the userform is correct before it is added to the spreedsheet. I do not know how to achieve this.

    2). I thought that I had the new worksheet opening at the 1st of every month completed, but instead of saying no to the question (do you want to open a new monthly workseet) a user answered yes and the program failed.

    I'm currently at my wit's end... as some of you already know I'm not as experienced at this as I would like to be but I am learning with your assistance. And I want to thank you all for everything you have helped me with this past two weeks.

    Once again here is my code Code moved to attachment by HansV since it was excessively long

    please note that since it did not work I commented it all out in case I needed it again later
    Attached Files Attached Files

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

    Re: Need help correcting error's in code (excel/vba 2003)

    Hans, I thought I did use the tags did they not work,? I really do not understand what you mean by checking to ensure the data is correct first before putting it into the workbook. my books don't show anything like that in the index's. so i have nothing to go off of.

    question2:

    if the person is on the data entry worksheet (just macro's to open up the userform) and then states to open a new workbook it opens that workbook which is incorrect. I need it to open the monthly workbook only and copy it from perevious months.

    I am truly sorry my friend I thought i did put the code in tags

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

    Re: Need help correcting error's in code (excel/vba 2003)

    Your code performs a check, then inserts data, then performs another check, inserts data, etc.
    You should move ALL code that inserts data into the worksheet to the end of the procedure, below ALL code that performs checks.

    I'm not sure I understand the second problem, but instead of checking whether the date is on the 1st of the month, I'd use the check (that you already wrote) to see if the sheet for the current month already exists. If it does, activate that sheet, and if it doesn't, copy last month's sheet to a new sheet.

    You used [code and /code] but I have already told you twice that you should use <!t>
    Code:
     and <!t>

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

    Re: Need help correcting error's in code (excel/vba 2003)

    Hans

    Did you see that bright light in the sky, It was me, I finally figured out what you mean't (the light came on). data validation now works great. thank you

    QUESTION 2: is this what you are talking about?
    <code>
    Dim irow As Long
    Dim myDate As Date, newDate As Date, oldSheet As String, newSheet As String
    Dim ws As Worksheet
    Set ws = Worksheets("MAR2008")
    </code>

    So i'm missing the other ][ ok i get that , did I do it correctly this time?

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

    Re: Need help correcting error's in code (excel/vba 2003)

    Great! Yes, you used the tags correctly now.

    I meant this code:

    newDate = DateSerial(Year(Date), Month(Date), 1)
    newSheet = Format(newDate, "mmmyyyy")

    ' Check to see if the newSheet already exists
    For Each Sht In Worksheets
    If Sht.Name = newSheet Then
    MsgBox "This Monthly Sheet has already been opened"
    foundSheet = True
    Sht.Activate
    Exit For
    End If
    Next

    If foundSheet = False Then
    ' create new sheet here
    ...
    End If

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

    Re: Need help correcting error's in code (excel/vba 2003)

    This is what I currently now have in there:

    <code>
    Dim myDate As Date, newDate As Date, oldSheet As String, newSheet As String, TEMPLATE As String

    Dim Sht As Worksheet, foundSheet As Boolean
    If Day(Date) = 1 Then Exit Sub
    If MsgBox("Do you want to copy to the new month?", vbYesNo) = vbNo Then Exit Sub
    oldSheet = ActiveSheet.Name
    myDate = DateValue("1-" & oldSheet)
    newDate = DateSerial(Year(myDate), Month(myDate) + 1, 1)

    newDate = DateSerial(Year(Date), Month(Date), 1)
    newSheet = Format(newDate, "mmmyyyy")

    'Check to see if the newSheet already exists
    For Each Sht In Worksheets
    If Sht.Name = newSheet Then
    MsgBox "This Monthly Sheet has already been opened"
    foundSheet = True
    Sht.Activate
    Exit For
    End If
    Next

    If Not foundSheet Then
    ActiveSheet.Copy after:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = newSheet
    Range("A3:I3000").ClearContents
    End If
    End Sub
    </code>

    the problem is that it keep giving me an error at :

    <code>
    myDate = DateValue("1-" & oldSheet)
    </code>

    and I don't know why - When I put my curser over it it shows: oldsheet="MAR2008" (<----- not sure if this is considered code or not)

    any ideas

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

    Re: Need help correcting error's in code (excel/vba 2003)

    i'm now getting invalid outside procedure

    <code>
    If Day(Date) = 1 Then Exit Sub
    </code>

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

    Re: Need help correcting error's in code (excel/vba 2003)

    was able to correct that error but now getting type mismatch error 13 and it does not show me where the error is

    <code>
    Private Sub Workbook_Open()
    Dim myDate As Date, newDate As Date, oldSheet As String, newSheet As String
    Dim Sht As Worksheet, foundSheet As Boolean
    If Day(Date) = 1 Then Exit Sub
    If MsgBox("Do you want to copy to the new month?", vbYesNo) = vbNo Then Exit Sub
    oldSheet = ActiveSheet.Name

    'Check to see if the newSheet already exists
    For Each Sht In Worksheets
    If Sht.Name = newSheet Then
    MsgBox "This Monthly Sheet has already been opened"
    foundSheet = True
    Sht.Activate
    Exit For
    End If
    Next

    If Not foundSheet Then
    ActiveSheet.Copy after:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = newSheet
    Range("A3:I3000").ClearContents
    End If
    End Sub

    </code>

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

    Re: Need help correcting error's in code (excel/vba 2003)

    You don't assign a value to newSheet anywhere in this code.

    Do you really want to exit the procedure if the day is the 1st of the month?

Posting Permissions

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