subroutine

[Trevor3007]

In the 'red'code below , if the user chooses N, I need it to go to the 'green' code bit at the bottom just before the 'exit' so the cursor ends up in cell A2 & a msgbox dispalys to prompt the user with a message of advise. It works fine if Y is chosen?

Sub importdata()
'
' importdata Macro
''
Workbooks.Open Filename:="C:\Users\xyz\desktop\info\coffe_data.xlsx"
Range("A2:H3007").Select
Selection.Copy
Windows("test_lookup.xlsm").Activate
Range("A2").Select
ActiveSheet.Paste
Windows("lookup_data.xlsx").Activate
Application.CutCopyMode = False
ActiveWindow.Close
If MsgBox("Do You Want A Border Placed Around This Worksheet", vbYesNo + vbQuestion) = vbNo Then Exit Sub
Range("A1:N3007").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("A2").Select
Range("I1:I3007").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A2").Select
MsgBox "End Of Macro,Please Save The File To An Area Of your Choice If You Are Finished or Rerun Again."
End Sub

Any suggs?

KR
Trevor3007

 

[Trevor3007]

hi ,

Great news, you have sorted the error & it 'un/protects' the sheet.........however the cells that need to be protected , I can delete even though its says it is protected & I have checked that the applicable cells that be 'protected' that the 'box' is ticked.


aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaH