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]

If you are not using a password then you can omit it something like this

Workbooks("coffe_data.xlsx").Sheets("info").Protect

what is lap_lookup?

humph................... same error

lap_lookup is the name of the workbook which runs the code

MT
Trevor3007

 

[Scott T]

You need the workbooks object like in my example.

workbooks("workbook name here.xlsx").sheets("sheet name here").Protect

 

[Trevor3007]

You need the workbooks object like in my example.

workbooks("workbook name here.xlsx").sheets("sheet name here").Protect

hey scott t,

same error at same point :]

 

[Scott T]

What is the error code you are getting?


Is the sheet you are doing this on unprotected?

[COLOR=#ff0000]Selection.Borders(xlDiagonalDown).LineStyle = xlNone[/COLOR]

 

[Trevor3007]

What is the error code you are getting?


Is the sheet you are doing this on unprotected?

[COLOR=#ff0000]Selection.Borders(xlDiagonalDown).LineStyle = xlNone[/COLOR]

The sheet is unprotected.


What is the error code you are getting?

Selection.Borders(xlDiagonalDown).LineStyle = xlNone

 

[Scott T]

What is the actual error code that pops up like

Run time error 9
subscript out of range

 

[Trevor3007]

What is the actual error code that pops up like

Run time error 9
subscript out of range

hi,

yes that's the exact error code:-

Run time error 9
subscript out of range

 

[Scott T]

It is unable to find something, a worksheet, workbook, variable. Make sure everything is spelled correctly. The worksheet and workbooks names are the exactly the same no extra spaces. For example If your sheet is named "sheet 1" and your code says sheets("sheet1") you will get this error.

 

[Trevor3007]

It is unable to find something, a worksheet, workbook, variable. Make sure everything is spelled correctly. The worksheet and workbooks names are the exactly the same no extra spaces. For example If your sheet is named "sheet 1" and your code says sheets("sheet1") you will get this error.

hi,

sorry for the delay in replying...

it goes OK until it needs to put the 'protect' on and then error message:

'runtime error '438' object does not support this property or method' appears & highlights in yellow:-

Workbooks("lap_lookup.xlsm").Sheet("info").Protect

:/

 

[Scott T]

That should be sheets

Workbooks("lap_lookup.xlsm").Sheet[COLOR=#ff0000]s[/COLOR]("info").Protect