pause then continue

[Trevor3007]

hi,

this is my current code:

'
' importdata Macro
''workbooks("lap_lookup.xlsm").sheets("info").unProtect
    Workbooks.Open Filename:="C:\123\123_data.xlsx"
    Range("A2:H3007").Select
    Selection.Copy
    Windows("123_lookup.xlsm").Activate
    Range("A2").Select
    ActiveSheet.Paste
    Windows("123_data.xlsx").Activate
    Application.CutCopyMode = False
    ActiveWindow.Close
     If MsgBox("Do You Want A Border Placed Around This Worksheet", vbYesNo + vbQuestion) = vbYes Then
     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
 End If
  Range("A2").Select
 MsgBox "End Of Macro,Please Save The File To An Area Of your Choice If You Are Finished or Rerun Again."
 Workbooks("l123_lookup.xlsm").Sheets("info").Protect
 End Sub

I need to advise the user that they need to 'unprotect' the worksheet before continuing then press 'ok' to continue.
Have checked mrexcel site & the web but all seems rather long winded or not applicable.

many thanks for your help.
KR
Trevor3007

'

 

[RoryA]

Why not just have the code issue an Unprotect command, which will prompt for the password?

 

[Trevor3007]

Why not just have the code issue an Unprotect command, which will prompt for the password?

hi there,

thanks for your email. Yeah that would be great, but if you look at the code, it has this already. However to run the code, there is a 'user button' and therefore the button does not work when the protection is on :{ hence why a need this 'msgbox'

 

[RoryA]

Then how do you plan to run the code to tell them to unprotect? What should trigger it?

 

[Stiuart_W]

Protecting a worksheet doesn't stop a user from pressing a Form Control or drawing object with a macro assigned

 

[Trevor3007]

Why not just have the code issue an Unprotect command, which will prompt for the password?

hi
thank you for you email.

Yes that's true...but it stops the 'VB' working as the area that it copies data to is protected.


KR
Trevor

 

[Stiuart_W]

You need to unprotect the worksheet in 123_data.xlsx. Currently your code only unprotects the sheet Info in lap_lookup

 

[Trevor3007]

You need to unprotect the worksheet in 123_data.xlsx. Currently your code only unprotects the sheet Info in lap_lookup

Hi Stiuart_W ,

thank you for your email & help thereof :}

The data is copied into lap_lookup which is protected. so I am a tad :? (woos of being a newbie sorry ) as to where I would place the unprotect?

could you kindly insert and therefore I will then I can see the error of my ways.


MTIA
Trevor3007

 

[Stiuart_W]

I have changed/added the code in red. Change SHEETNAME to the name of the sheet you are woking on in the workbook the code opens (123_data.xlsx. By the way. Your code doesn't close that workbook again. you should consider doing that as well.

'
' importdata Macro
[B][COLOR=#ff0000]Workbooks("lap_lookup.xlsm").sheets("info").unProtect[/COLOR][/B]
    Workbooks.Open Filename:="C:\123\123_data.xlsx"
[B][COLOR=#ff0000]workbooks("123_data.xlsx").sheets("SHEETNAME").unProtect[/COLOR][/B]
    Range("A2:H3007").Select
    Selection.Copy
    Windows("123_lookup.xlsm").Activate
    Range("A2").Select
    ActiveSheet.Paste
    Windows("123_data.xlsx").Activate
    Application.CutCopyMode = False
    ActiveWindow.Close
     If MsgBox("Do You Want A Border Placed Around This Worksheet", vbYesNo + vbQuestion) = vbYes Then
     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
 End If
  Range("A2").Select
 MsgBox "End Of Macro,Please Save The File To An Area Of your Choice If You Are Finished or Rerun Again."
 [COLOR=#ff0000][B]Workbooks("lap_lookup.xlsm").Sheets("info").Protect
Workbooks("123_data.xlsx").Sheets("SHEETNAME").Protect[/B][/COLOR]
 End Sub

 

[Trevor3007]

WOW... that's IT..... FAAANtastic. you certainly know your stuff. Thank you sooo much.

KR
Trevor3007