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

    Found new error, need assistance with (excel/vba 2003 )

    when opening a new sheet with the code below I have it so that a msgbox pops up asking them if they want to open a new monthy worksheet. if vbno then exit. if they answer yes and the day of the month is the first it will open a new worksheet.

    my problem / errror is this - if someone else already opened a new worksheet for the month (already exists) then somebody else accidentally answers yes to open a new worksheet it gives them an error and shows my vba code. I can;t let this occur

    any ideas

    Private Sub Workbook_Open()

    Dim myDate As Date, newDate As Date, oldSheet As String, newSheet As String
    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)
    newSheet = Format(newDate, "mmmyyyy")
    ActiveSheet.Copy after:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = newSheet
    Range("A3:I3000").ClearContents
    End Sub

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

    Re: Found new error, need assistance with (excel/vba 2003 )

    You could check whether the new sheet already exists:

    Private Sub Workbook_Open()
    Dim myDate As Date, newDate As Date, oldSheet As String, newSheet As String
    Dim wsh As Worksheet
    If Day(Date) <> 1 Then Exit Sub
    oldSheet = ActiveSheet.Name
    myDate = DateValue("1-" & oldSheet)
    newDate = DateSerial(Year(myDate), Month(myDate) + 1, 1)
    newSheet = Format(newDate, "mmmyyyy")
    ' Suppress error messages
    On Error Resume Next
    ' Try to refer to the new sheet
    Set wsh = Worksheets(newSheet)
    On Error GoTo 0
    ' If wsh is not Nothing then the sheet exists already
    If Not wsh Is Nothing Then Exit Sub
    If MsgBox("Do you want to copy to the new month?", vbYesNo) = vbNo Then Exit Sub
    ActiveSheet.Copy After:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = newSheet
    Range("A3:I3000").ClearContents
    End Sub

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Found new error, need assistance with (excel/v

    Possibly the best approach here is not to allow the second user to use the workbook. You could have a function that checks if the workbook is already open.

    An example of the code you could use is here:

    Determine if a workbook is already open using VBA in Microsoft Excel

    You could adapt it so that a message pops up and gives a critical warning stating "Workbook already open...Now Closing!"
    Jerry

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

    Re: Found new error, need assistance with (excel/vba 2003 )

    hans - imgetting a type mismatch error onthe following

    myDate = DateValue("1-" & oldSheet)

    why?

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

    Re: Found new error, need assistance with (excel/vba 2003 )

    That is existing code, so it has nothing to do with the new additions. It's probably the same error as in <post:=696,807>post 696,807</post:>.

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

    Re: Found new error, need assistance with (excel/vba 2003 )

    one more thing for the day and i'm done. How would you check to ensure that somebody actually typed in a date in a date field. I just had somebody try it out and they mistyped the time instead of the date and it plugged ii into the database.

    ws.Cells(irow, 6).Value = Me.txtACTDATE.Value
    If Me.txtACTDATE.Value = "" Then
    Me.txtACTDATE.SetFocus
    MsgBox "Please enter the active date as requested!"
    Exit Sub
    if metxtactdate.value
    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: Found new error, need assistance with (excel/vba 2003 )

    Hans the only problem with that is that it also copies the data entry worksheet that only contains the macro for the user to click on (it allows them to open up the userform where they input their data). I don't need two of them just one. but i do need the monthly worksheet copied

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

    Re: Found new error, need assistance with (excel/vba 2003 )

    You should perform the check before entering the value into the worksheet.
    You could test like this:

    If Not IsDate(Me.txtACTDATE) Then
    Me.txtACTDATE.SetFocus
    MsgBox "Please enter a valid date!"
    Exit Sub
    End If
    If CDate(Me.txtACTDATE) < 1 Then
    Me.txtACTDATE.SetFocus
    MsgBox "Please enter a date, not a time!"
    Exit Sub
    End If
    ...

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

    Re: Found new error, need assistance with (excel/vba 2003 )

    Check that your code runs in the correct worksheet.

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

    Re: Found new error, need assistance with (excel/vba 2003 )

    I need to know how to check column c for any numeric duplicate numbers. If there are duplicates then msgbox. else continue.

    Here's the catch, what i have now stops the user with a message box like i want but it still inputs the data into the spreedsheet.

    any ideas or assistance with this code would be appreciated.

    [code
    Set rng = ws.Range("C:C").Find(What:=strValue, LookIn:=xlValues, LookAt:=xlWhole)
    If Not rng Is Nothing Then
    Me.txtCA7.SetFocus
    MsgBox "This ca7 Number has already been used!"
    Exit Sub
    End If
    ws.Cells(irow, 3) = strValue
    'ws.Cells(irow, 3).Value = Me.txtSdnumber.Value
    If Me.txtCA7 = "" Then
    Me.txtCA7.SetFocus
    MsgBox "Please enter the CA7 Number!"
    Exit Sub
    End If
    If IsNumeric(ws.Cells(irow, 3).Value) Then

    Me.txtCA7.SetFocus
    Else: MsgBox "please enter the correct CA7 number!"
    End If

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

    Re: Found new error, need assistance with (excel/vba 2003 )

    Your code exits the procedure after displaying the message box, so I don't see how it could enter the value into the worksheet unless that has already happened before the code that you posted.

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

    Re: Found new error, need assistance with (excel/vba 2003 )

    Hans - whats happening is that it inputs the previous data that the user typed in the userform.

    so columns A and B are filled with data and everything else is blank still

    any ideas here's the code:

    [code

    Private Sub cmdOneshot_Click()

    Dim strValue As String
    Dim rng As Range
    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")
    'find first empty row in database
    irow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row
    'check for a job name!
    If Trim(Me.txtOneName.Value) = "" Then
    Me.txtOneName.SetFocus
    MsgBox "Please enter the OneShot to be entered into the database!"
    Me.txtOneName.SetFocus
    Exit Sub
    End If
    'copy the data to the database
    ws.Cells(irow, 1).Value = Date
    ws.Cells(irow, 2).Value = Me.txtOneName.Value
    If Me.txtOneName.Value = "" Then
    Me.txtOneName.SetFocus
    MsgBox "Please enter the oneshot job name!"
    Exit Sub
    End If

    Set rng = ws.Range("C:C").Find(What:=strValue, LookIn:=xlValues, LookAt:=xlWhole)
    If Not rng Is Nothing Then
    Me.txtCA7.SetFocus
    MsgBox "This ca7 Number has already been used!"
    Exit Sub
    End If
    ws.Cells(irow, 3) = strValue
    'ws.Cells(irow, 3).Value = Me.txtSdnumber.Value
    If Me.txtCA7 = "" Then
    Me.txtCA7.SetFocus
    MsgBox "Please enter the CA7 Number!"
    Exit Sub
    End If
    If IsNumeric(ws.Cells(irow, 3).Value) Then

    Me.txtCA7.SetFocus
    Else: MsgBox "please enter the correct CA7 number!"
    End If

    /code]

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

    Re: Found new error, need assistance with (excel/vba 2003 )

    In the lines

    ws.Cells(irow, 1).Value = Date
    ws.Cells(irow, 2).Value = Me.txtOneName.Value
    If Me.txtOneName.Value = "" Then
    Me.txtOneName.SetFocus
    MsgBox "Please enter the oneshot job name!"
    Exit Sub
    End If

    you FIRST enter the data into columns A and B and THEN check if the name has been entered. you should check first whether ALL conditions are met, and only then enter data into the worksheet.

    Moreover, you define a variable strValue but you don't assign a value to it anywhere in the code.

    Note: to format the code with a fixed-width font, put <!t>
    Code:
    <!/t> before it and <!t>
    <!/t> after it.

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

    Re: Found new error, need assistance with (excel/vba 2003 )

    not sure I understand.

    [code
    If Me.txtOneName.Value = "" Then
    Me.txtOneName.SetFocus
    MsgBox "Please enter the oneshot job name!"
    Exit Sub
    ws.Cells(irow, 1).Value = Date
    ws.Cells(irow, 2).Value = Me.txtOneName.Value
    /code]

    is this what you mean?

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

    Re: Found new error, need assistance with (excel/vba 2003 )

    You need to insert End If below the Exit Sub line. And I think you want to check for duplicates BEFORE entering anything into the worksheet.

    Please read the remark about the <!t>
    Code:
     and <!t>
    tags in my previous reply.

Posting Permissions

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