VBA Runtime error

[greeced2]

Hello Everyone,

Here is my code:

What I'm trying to do under VBA is when J5 = NO, change M5 to 0. That I can accomplish. However if I do add that code, the proceeding hide rows gives me the run time

1004 error:

Unable to set the hidden property of the Range Class

However if I remove the problematic highlight and manually change the value of m5, it hides/unhides without issue.

Any Ideas??

Sub Worksheet_Change(ByVal Target As Range)
Dim A1 As Range
    Set A1 = Range("J5")
    If Not Intersect(Target, A1) Is Nothing Then
        Application.EnableEvents = False
            Target.Value = UCase(Target.Value)
        Application.EnableEvents = True
    End If
[COLOR=#ff0000]If Range("J5") = "NO" Then      [/COLOR]
[COLOR=#ff0000]Range("m5") = 0                     [/COLOR]
[COLOR=#ff0000]End If[/COLOR]
If Range("M5") = "0" Then
Rows("59:89").EntireRow.Hidden = False
Rows("59:89").EntireRow.Hidden = True
End If

 

[greeced2]

I forgot to mention, when the run-time error happens, it freezes excel and the only thing I can do is end the task in task manager.

 

[Fluff]

How about

Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("J5")) Is Nothing Then
      Application.EnableEvents = False
      Target.Value = UCase(Target.Value)
      If Target.Value = "NO" Then Target.Offset(, 3).Value = 0
      Application.EnableEvents = True
   End If
   If Range("M5") = "0" Then
      Rows("59:89").EntireRow.Hidden = False
      Rows("59:89").EntireRow.Hidden = True
   End If
End Sub

 

[greeced2]

Works Great! Thanks

 

[Fluff]

Your welcome & thanks for the feedback

 

[greeced2]

One final question and I've searched everywhere.

I need an if then or else statement that will check an https:// SharePoint url to see if a file exists. If it does, then a msgbox that will ask if you would like to overwrite. If not then exit Sub.

The problem I'm experiencing here is:

Url access to SharePoint does not require a password because it's already part of the proxy settings.

If I backdoor into the SharePoint site via the network path it tends to break VBA and rarely works because a password is required.

As this Excel file will be used by multiple users I don't want to use that method.

The DIR function doesn't work with URLs... So.. any ideas?

 

[Fluff]

As this is a completely different question, you will need to start a new thread.

 

[greeced2]

Ok thanks. Will do