Run Time Error VBA

[anand3dinesh]

Hi There,

please can anyone tell me what is wrong with my code.

i need this code to do the job to fix the sheetname in a selected cell and this works fine. but when click cancel it should come out from the sub, but it gives runtime error.
your reply will be much appriciatted


Private Sub FixSheetName_Click()

Dim sh As Worksheet
Dim rng As Range
Set rng = Application.InputBox(prompt:="Select Cell to Fix Name", Title:="Select Cell", Type:=8)

If Not rng Is Nothing Then
For Each sh In Sheets
sh.Unprotect
sh.Range(rng.Address).Value = sh.Name
Next sh
Else
Exit Sub

End If
End Sub

Thanks in Advance.

 

[Fluff]

How about

Private Sub FixSheetName_Click()
   Dim sh As Worksheet
   Dim rng As Range
   
   On Error Resume Next
   Set rng = Application.InputBox("Select Cell to Fix Name", "Select Cell", , , , , , 8)
   On Error GoTo 0
   If Not rng Is Nothing Then
      For Each sh In Sheets
         sh.Unprotect
         sh.Range(rng.Address).Value = sh.Name
      Next sh
   End If
End Sub

 

[anand3dinesh]

Thanks Fluff, Woking smooth

 

[Fluff]

You're welcome & thanks for the feedback.