#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    2
    Rep Power
    0

    COPY/Paste of decimal values in sheet


    hi ,
    i am facing one problem while writing new code to the already exising vba code in my office,please help me...
    in one sheet called resource worksheet for one column only it has to allow whole numbers,so already data validation was written for that column in the sheet,but while copy/paste of decimal or negative other than whole it is allowing...so i wrote code for that particular sheet which has already some code in it as follows,my code is in double quotes

    it is giving error as "Method 'Range' of object '_Global' failed" ,while debugging i found that it is going to the logerror function after giving msg written in the code and not clearing the c.value,as it is going to the other page ,i will also give that page after the below block

    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Worksheet_Change_err
    " For Each c In Target
    If (c.Value Like "*.*" Or c.Value Like "*-*") And c.Column = 3 Then
    If boolinvalid = False Then
    boolinvalid = True
    MsgBox "The number can only be a whole number between 0 and 100,000,000,000,000,000", vbCritical
    End If
    c.Value = ""
    ElseIf (Not IsNumeric(c.Value)) And c.Column = 3 Then
    c.Value = ""
    End If
    Next"
    5 Dim cell As Range
    10 For Each cell In Target
    15 If typeName(Target.Value) = "String" Then 'added to exclude the "add resource" function
    20 If Target.row >= FIRST_ROW And Not Intersect(getResourceHeadingCell("Resource Name").EntireColumn, Target) Is Nothing And Not Trim(Target.Value) = "" Then
    22 If isCollectByRegionOrOu Then
    25 Application.EnableEvents = False
    26 setDefaultCountry (Target.row)
    27 'setDefaultOU (Target.row)
    35 Application.EnableEvents = True
    37 End If
    40 End If
    45 End If
    50 Next cell
    Exit Sub
    Worksheet_Change_err:
    logError "Worksheet_Change resource sheet", Erl, Err.description, Err.number
    End Sub

    ------------------after c.value it is coming to this and not clearing the value ,this is my problem can you please help me on this ,please....

    Public Sub logError(Optional subOrFuncName As String, Optional lineNum As Integer, Optional description As String, Optional number As Integer)
    'logs errors to the errorLog worksheet
    On Error Resume Next
    Dim logRng As Range
    checkForLog
    Set logRng = Worksheets("ErrorLog").Range("A2")
    logRng.EntireRow.Insert
    logRng.Cells(0, 1).Value = Now
    logRng.Cells(0, 2).Value = subOrFuncName
    logRng.Cells(0, 3).Value = lineNum
    logRng.Cells(0, 4).Value = description
    logRng.Cells(0, 5).Value = number
    Worksheets("ErrorLog").Rows(200).Delete
    End Sub
    Private Sub checkForLog()
    'checks for the existance of the errorLog worksheet and creates it if needed
    Dim errorLog As Worksheet
    On Error GoTo noSheet
    Set errorLog = Worksheets("ErrorLog")
    Exit Sub
    noSheet:
    On Error Resume Next
    Application.ScreenUpdating = False
    Worksheets.Add(After:=Worksheets(Worksheets.count)).name = "ErrorLog"
    With Range("ErrorLog!A1")
    .Value = "Error Date / Time"
    .EntireColumn.ColumnWidth = 15
    .EntireColumn.HorizontalAlignment = xlLeft
    .Cells(1, 2).Value = "Sub or Function Name"
    .Cells(1, 2).EntireColumn.AutoFit
    .Cells(1, 2).EntireColumn.ColumnWidth = 25
    .Cells(1, 2).EntireColumn.HorizontalAlignment = xlLeft
    .Cells(1, 3).Value = "Line Number"
    .Cells(1, 3).EntireColumn.AutoFit
    .Cells(1, 3).EntireColumn.ColumnWidth = 15
    .Cells(1, 3).EntireColumn.HorizontalAlignment = xlLeft
    .Cells(1, 4).Value = "Description"
    .Cells(1, 4).EntireColumn.AutoFit
    .Cells(1, 4).EntireColumn.ColumnWidth = 25
    .Cells(1, 4).EntireColumn.HorizontalAlignment = xlLeft
    .Cells(1, 5).Value = "Error Code"
    .Cells(1, 5).EntireColumn.AutoFit
    .Cells(1, 5).EntireColumn.ColumnWidth = 15
    .Cells(1, 5).EntireColumn.HorizontalAlignment = xlLeft
    End With
    Worksheets("ErrorLog").Visible = xlSheetVeryHidden
    Application.ScreenUpdating = True
    End Sub
    Public Sub showErrorLog()
    On Error GoTo noLog
    Worksheets("ErrorLog").Visible = xlSheetVisible
    Worksheets("ErrorLog").Select
    Exit Sub

    noLog:
    MsgBox "There are no errors to display"
    End Sub
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2010
    Posts
    116
    Rep Power
    158
    Originally Posted by mounika
    hi ,
    i am facing one problem while writing new code to the already exising vba code in my office,please help me...
    in one sheet called resource worksheet for one column only it has to allow whole numbers,so already data validation was written for that column in the sheet,but while copy/paste of decimal or negative other than whole it is allowing...so i wrote code for that particular sheet which has already some code in it as follows,my code is in double quotes

    it is giving error as "Method 'Range' of object '_Global' failed" ,while debugging i found that it is going to the logerror function after giving msg written in the code and not clearing the c.value,as it is going to the other page ,i will also give that page after the below block

    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Worksheet_Change_err
    " For Each c In Target
    If (c.Value Like "*.*" Or c.Value Like "*-*") And c.Column = 3 Then
    If boolinvalid = False Then
    boolinvalid = True
    MsgBox "The number can only be a whole number between 0 and 100,000,000,000,000,000", vbCritical
    End If
    c.Value = ""
    ElseIf (Not IsNumeric(c.Value)) And c.Column = 3 Then
    c.Value = ""
    End If
    Next"
    5 Dim cell As Range
    10 For Each cell In Target
    15 If typeName(Target.Value) = "String" Then 'added to exclude the "add resource" function
    20 If Target.row >= FIRST_ROW And Not Intersect(getResourceHeadingCell("Resource Name").EntireColumn, Target) Is Nothing And Not Trim(Target.Value) = "" Then
    22 If isCollectByRegionOrOu Then
    25 Application.EnableEvents = False
    26 setDefaultCountry (Target.row)
    27 'setDefaultOU (Target.row)
    35 Application.EnableEvents = True
    37 End If
    40 End If
    45 End If
    50 Next cell
    Exit Sub
    Worksheet_Change_err:
    logError "Worksheet_Change resource sheet", Erl, Err.description, Err.number
    End Sub

    ------------------after c.value it is coming to this and not clearing the value ,this is my problem can you please help me on this ,please....

    Public Sub logError(Optional subOrFuncName As String, Optional lineNum As Integer, Optional description As String, Optional number As Integer)
    'logs errors to the errorLog worksheet
    On Error Resume Next
    Dim logRng As Range
    checkForLog
    Set logRng = Worksheets("ErrorLog").Range("A2")
    logRng.EntireRow.Insert
    logRng.Cells(0, 1).Value = Now
    logRng.Cells(0, 2).Value = subOrFuncName
    logRng.Cells(0, 3).Value = lineNum
    logRng.Cells(0, 4).Value = description
    logRng.Cells(0, 5).Value = number
    Worksheets("ErrorLog").Rows(200).Delete
    End Sub
    Private Sub checkForLog()
    'checks for the existance of the errorLog worksheet and creates it if needed
    Dim errorLog As Worksheet
    On Error GoTo noSheet
    Set errorLog = Worksheets("ErrorLog")
    Exit Sub
    noSheet:
    On Error Resume Next
    Application.ScreenUpdating = False
    Worksheets.Add(After:=Worksheets(Worksheets.count)).name = "ErrorLog"
    With Range("ErrorLog!A1")
    .Value = "Error Date / Time"
    .EntireColumn.ColumnWidth = 15
    .EntireColumn.HorizontalAlignment = xlLeft
    .Cells(1, 2).Value = "Sub or Function Name"
    .Cells(1, 2).EntireColumn.AutoFit
    .Cells(1, 2).EntireColumn.ColumnWidth = 25
    .Cells(1, 2).EntireColumn.HorizontalAlignment = xlLeft
    .Cells(1, 3).Value = "Line Number"
    .Cells(1, 3).EntireColumn.AutoFit
    .Cells(1, 3).EntireColumn.ColumnWidth = 15
    .Cells(1, 3).EntireColumn.HorizontalAlignment = xlLeft
    .Cells(1, 4).Value = "Description"
    .Cells(1, 4).EntireColumn.AutoFit
    .Cells(1, 4).EntireColumn.ColumnWidth = 25
    .Cells(1, 4).EntireColumn.HorizontalAlignment = xlLeft
    .Cells(1, 5).Value = "Error Code"
    .Cells(1, 5).EntireColumn.AutoFit
    .Cells(1, 5).EntireColumn.ColumnWidth = 15
    .Cells(1, 5).EntireColumn.HorizontalAlignment = xlLeft
    End With
    Worksheets("ErrorLog").Visible = xlSheetVeryHidden
    Application.ScreenUpdating = True
    End Sub
    Public Sub showErrorLog()
    On Error GoTo noLog
    Worksheets("ErrorLog").Visible = xlSheetVisible
    Worksheets("ErrorLog").Select
    Exit Sub

    noLog:
    MsgBox "There are no errors to display"
    End Sub
    wow way to complex for me (full of the cold)

    why do you need whole numbers

    is it for show or some calculation on the sheet

    here to talk
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    2
    Rep Power
    0
    hii, yes we need whole numbers for some calculation purpose ,actually we wrote the code in an event worksheet change as u see the below red color code..but the error log is supressing the code and the sheet is hanging ,is there any possibility of writing the code that doest not accept decimals while copy/paste ...

    Many thanks for your reply

    Mounika

IMN logo majestic logo threadwatch logo seochat tools logo