macro runtime error

P

plost33

Well-known Member
Joined
Oct 2, 2008
Messages
866
I have the following macro in a spreadsheet in 07 excel:


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
If Not Intersect(Target, Range("D12:D36")) Is Nothing Then
Application.EnableEvents = False
Select Case Target.Value
Case "Weather-NP": Target.Value = "WOW"
Case "Bell Run-NP": Target.Value = "BR"
Case "Survey/Diagnostics-A": Target.Value = "SD-A"
Case "Diamond Wire Cutter-A": Target.Value = "DWC-A"
Case "Diver Survey-A": Target.Value = "DS-A"
Case "Chop Saw-A": Target.Value = "CS-A"
Case "Deck Removal-A": Target.Value = "DR-A"
Case "Structure Removal-A": Target.Value = "SR-A"
Case "Excavate-A": Target.Value = "EXC-A"
Case "Strongback-I": Target.Value = "SB"
Case "Excavate-I": Target.Value = "EXC-I"
Case "Survey/Diagnostic-I": Target.Value = "SD-I"
Case "Annular Interval Tester-I": Target.Value = "IAT-I"
Case "Diver Survey-I": Target.Value = "DS-I"
Case "Conventional Hot Tap-I": Target.Value = "CHT"
Case "Direct Hot Tap-I": Target.Value = "DHT"
Case "Bleed & Kill-I": Target.Value = "BK"
Case "Shear Operation-I": Target.Value = "SO"
Case "Diamond Wire Cutter-I": Target.Value = "DWC-I"
Case "Chop Saw-I": Target.Value = "CS-I"
Case "Porta-Lathe-I": Target.Value = "PL"
Case "Wedding Cake-I": Target.Value = "WC"
Case "Install Well Head-I": Target.Value = "IWH"
Case "Survey/Diagnostic-TA": Target.Value = "SD-TA"
Case "Test Surf/Prod. Annulus-TA": Target.Value = "TCA-TA"
Case "Wireline Bottom-TA": Target.Value = "WL-B"
Case "Establish Injection/Circulation Bottom-TA": Target.Value = "EIR-B"
Case "Perf Bottom-TA": Target.Value = "Perf-B"
Case "Cement Bottom Plug-TA": Target.Value = "CMT-B"
Case "Test CMT Bottom Plug-TA": Target.Value = "TCP-B"
Case "Wireline Intermediate-TA": Target.Value = "WL-I"
Case "Establish Injection/Circulation Intermediate-TA": Target.Value = "EIR-I"
Case "Perf Intermediate-TA": Target.Value = "Perf-I"
Case "Cement Intermediate Plug-TA": Target.Value = "CMT-I"
Case "Test CMT Intermediate Plug-TA": Target.Value = "TCP-I"
Case "Wireline Surface-TA": Target.Value = "WL-S"
Case "Establish Injection/Circulation Surface-TA": Target.Value = "EIR-S"
Case "Perf Surface-TA": Target.Value = "Perf-S"
Case "Cement Surface Plug-TA": Target.Value = "CMT-S"
Case "Test CMT Surface Plug-TA": Target.Value = "TCP-S"
Case "Install Tester-TA": Target.Value = "IAT-TA"
Case "Grout Csg Annulus-TA": Target.Value = "GCA"
Case "Cut & Pull Csg-PA": Target.Value = "CPC"
Case "Debris Clearance-PA": Target.Value = "DC"
Case "Survey/Diagnostic-PA": Target.Value = "SD-PA"
Case "Deck Removal-PA": Target.Value = "DR-PA"
Case "Diver Survey-PA": Target.Value = "DS-PA"
Case "Mesotech-PA": Target.Value = "Meso"
Case "Trawling Site Clearance-PA": Target.Value = "TSC"
Case "Exit Survey-PA": Target.Value = "ES"
Case "Pipeline Survey-PA": Target.Value = "PS"
Case "Mechanical Downtime-NP": Target.Value = "MDT"
Case "Vessel Downtime-NP": Target.Value = "VDT"
Case "Regulatory-NP": Target.Value = "REG"
Case "Mobe/Demobe-NP": Target.Value = "MDM"
Case "Health Safety Environment-NP": Target.Value = "HSE"
Case "Waiting on Orders-NP": Target.Value = "WOO"
Case "Other-NP": Target.Value = "OTHER"
End Select
Application.EnableEvents = True
ElseIf Not Intersect(Target, Range("E12:F36")) Is Nothing Then
Application.EnableEvents = False
Target.Value = WorksheetFunction.MRound(Target.Value, 15 / (60 * 24))
If Target.Column = 6 Then
With Target.Offset(1, -1)
.NumberFormat = "hh:mm"
.Value = Target.Value
End With
End If
Application.EnableEvents = True
End If
End Sub




The Macro works great. But when I save the same excel file into 03 version of Excel, I am getting a run time error.

The message reads: run time error '438': object doesn't support this property or method.

I then have the options of: end, debug, help.

If I select Debug It highlights the row in the macro that reads:

Target.Value = WorksheetFunction.MRound(Target.Value, 15 / (60 * 24))



If anyone can please assist me that would be great. I am not a VBA expert and could use some help.
 
Sort by date Sort by votes
Sorry but I can only replicate that error if the cells in column E are locked.
 
Upvote 0

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
THE MACRO RUNS FINE IF THE ENTIRE SHEET IS UNPROTECTED. oNCE i PROTECT THE SHEET, EVEN WITH THE REFRENCED CELLS UNLOCKED THE MACRO GETS THE ERROR.
 
Upvote 0
Vog: even with the sheet protected I can enter data into column e and the macro will round the time as disired. But anytime any time is entered into F I get that error. wierd.


does it have anything to do with the fact that once the time in f is rounded, it is suppost to insert the new value into the next cell below in column e?


here is the code as it sits now:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
If Not Intersect(Target, Range("D12:D36")) Is Nothing Then
Application.EnableEvents = False
Select Case Target.Value
Case "Weather-NP": Target.Value = "WOW-NP"
Case "Bell Run-NP": Target.Value = "BR-NP"
Case "Survey/Diagnostics-A": Target.Value = "SD-A"
Case "Diamond Wire Cutter-A": Target.Value = "DWC-A"
Case "Diver Survey-A": Target.Value = "DS-A"
Case "Chop Saw-A": Target.Value = "CS-A"
Case "Deck Removal-A": Target.Value = "DR-A"
Case "Structure Removal-A": Target.Value = "SR-A"
Case "Excavate-A": Target.Value = "EXC-A"
Case "Strongback-I": Target.Value = "SB-I"
Case "Excavate-I": Target.Value = "EXC-I"
Case "Survey/Diagnostic-I": Target.Value = "SD-I"
Case "Annular Interval Tester-I": Target.Value = "AIT-I"
Case "Diver Survey-I": Target.Value = "DS-I"
Case "Conventional Hot Tap-I": Target.Value = "CHT-I"
Case "Direct Hot Tap-I": Target.Value = "DHT-I"
Case "Bleed & Kill-I": Target.Value = "BK-I"
Case "Shear Operation-I": Target.Value = "SO-I"
Case "Diamond Wire Cutter-I": Target.Value = "DWC-I"
Case "Chop Saw-I": Target.Value = "CS-I"
Case "Porta-Lathe-I": Target.Value = "PL-I"
Case "Wedding Cake-I": Target.Value = "WC-I"
Case "Install Well Head-I": Target.Value = "IWH-I"
Case "Survey/Diagnostic-TA": Target.Value = "SD-TA"
Case "Test Surf/Prod. Annulus-TA": Target.Value = "TCA-TA"
Case "Wireline Bottom-TA": Target.Value = "WL-B"
Case "Establish Injection/Circulation Bottom-TA": Target.Value = "EIR-B"
Case "Perf Bottom-TA": Target.Value = "PERF-B"
Case "Cement Bottom Plug-TA": Target.Value = "CMT-B"
Case "Test CMT Bottom Plug-TA": Target.Value = "TCP-B"
Case "Wireline Intermediate-TA": Target.Value = "WL-I"
Case "Establish Injection/Circulation Intermediate-TA": Target.Value = "EIR-I"
Case "Perf Intermediate-TA": Target.Value = "PERF-I"
Case "Cement Intermediate Plug-TA": Target.Value = "CMT-I"
Case "Diver Survey-TA": Target.Value = "DS-TA"
Case "Wireline Surface-TA": Target.Value = "WL-S"
Case "Establish Injection/Circulation Surface-TA": Target.Value = "EIR-S"
Case "Perf Surface-TA": Target.Value = "PERF-S"
Case "Cement Surface Plug-TA": Target.Value = "CMT-S"
Case "Structure Removal-PA": Target.Value = "SR-PA"
Case "Install Tester-TA": Target.Value = "AIT-TA"
Case "Grout Csg Annulus-TA": Target.Value = "GCA-TA"
Case "Cut & Pull Csg-PA": Target.Value = "CPC-PA"
Case "Debris Clearance-PA": Target.Value = "DC-PA"
Case "Survey/Diagnostic-PA": Target.Value = "SD-PA"
Case "Deck Removal-PA": Target.Value = "DR-PA"
Case "Diver Survey-PA": Target.Value = "DS-PA"
Case "Mesotech-PA": Target.Value = "MESO-PA"
Case "Trawling Site Clearance-PA": Target.Value = "TSC-PA"
Case "Exit Survey-PA": Target.Value = "ES-PA"
Case "Pipeline Survey-PA": Target.Value = "PS-PA"
Case "Mechanical Downtime-NP": Target.Value = "MDT-NP"
Case "Vessel Downtime-NP": Target.Value = "VDT-NP"
Case "Regulatory-NP": Target.Value = "REG-NP"
Case "Mobe/Demobe-NP": Target.Value = "MDM-NP"
Case "Health Safety Environment-NP": Target.Value = "HSE-NP"
Case "Waiting on Orders-NP": Target.Value = "WOO-NP"
Case "Other-NP": Target.Value = "OTHER-NP"
End Select
Application.EnableEvents = True
ElseIf Not Intersect(Target, Range("E12:F36")) Is Nothing Then
Application.EnableEvents = False
Target.Value = WorksheetFunction.Floor(Target.Value + 7.5 / 1440, 15 / 1440)
If Target.Column = 6 Then
With Target.Offset(1, -1)
.NumberFormat = "hh:mm"
.Value = Target.Value
End With
End If
Application.EnableEvents = True
End If
End Sub
 
Upvote 0
Try adding the lines in red and changing abc to the actual password

Rich (BB code): 
ElseIf Not Intersect(Target, Range("E12:F36")) Is Nothing Then
    Application.EnableEvents = False
    Me.Unprotect Password:="abc"
    Target.Value = WorksheetFunction.Floor(Target.Value + 7.5 / 1440, 15 / 1440)
    If Target.Column = 6 Then
        With Target.Offset(1, -1)
            .NumberFormat = "hh:mm"
            .Value = Target.Value
        End With
    End If
    Me.Protect Password:="abc"
    Application.EnableEvents = True
End If
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
macro question
Replies
4
Views
434
plost33
P
P
  • Question
runtime error macro
Replies
2
Views
448
Andrew Poulsom
Andrew Poulsom

Forum statistics

Threads
1,216,073
Messages
6,128,644
Members
449,461
Latest member
kokoanutt

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top