December 14th, 2011, 06:18 AM
COPY/Paste of decimal values in sheet
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
c.Value = ""
ElseIf (Not IsNumeric(c.Value)) And c.Column = 3 Then
c.Value = ""
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
logError "Worksheet_Change resource sheet", Erl, Err.description, Err.number
------------------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
Set logRng = Worksheets("ErrorLog").Range("A2")
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
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")
On Error Resume Next
Application.ScreenUpdating = False
Worksheets.Add(After:=Worksheets(Worksheets.count)).name = "ErrorLog"
.Value = "Error Date / Time"
.EntireColumn.ColumnWidth = 15
.EntireColumn.HorizontalAlignment = xlLeft
.Cells(1, 2).Value = "Sub or Function Name"
.Cells(1, 2).EntireColumn.ColumnWidth = 25
.Cells(1, 2).EntireColumn.HorizontalAlignment = xlLeft
.Cells(1, 3).Value = "Line Number"
.Cells(1, 3).EntireColumn.ColumnWidth = 15
.Cells(1, 3).EntireColumn.HorizontalAlignment = xlLeft
.Cells(1, 4).Value = "Description"
.Cells(1, 4).EntireColumn.ColumnWidth = 25
.Cells(1, 4).EntireColumn.HorizontalAlignment = xlLeft
.Cells(1, 5).Value = "Error Code"
.Cells(1, 5).EntireColumn.ColumnWidth = 15
.Cells(1, 5).EntireColumn.HorizontalAlignment = xlLeft
Worksheets("ErrorLog").Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
Public Sub showErrorLog()
On Error GoTo noLog
Worksheets("ErrorLog").Visible = xlSheetVisible
MsgBox "There are no errors to display"
December 23rd, 2011, 06:30 AM
wow way to complex for me (full of the cold)
Originally Posted by mounika
why do you need whole numbers
is it for show or some calculation on the sheet
here to talk
December 25th, 2011, 11:54 PM
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