msg box to ask where file is located

[Trevor3007]

hi,

I have already have vb code that opens a file etc. but I need to send this worksheet onto users ( this location will change from user to user & as will the file name). When the user 'clicks' on the macro button , they need to point to where their applicable file is located and then the rest of the VB will kick in?

Any suggs?

KR
Trevor3007

 

[alansidman]

Post your current VBA so that we can modify it appropriately

 

[Trevor3007]

Post your current VBA so that we can modify it appropriately

Many thanks for your reply....VB as follows:-


Sub importdata()
'
' importdata Macro
''
Workbooks.Open Filename:="C:\Users\xyz\123\Laps\lookup_data.xlsx"
Range("A2:H3007").Select
Selection.Copy
Windows("Lap_lookup.xlsm").Activate
Range("A2").Select
ActiveSheet.Paste
Windows("lookup_data.xlsx").Activate
Application.CutCopyMode = False
ActiveWindow.Close
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
End Sub

also find that the above is a tad slow .... any suggs to speed up? I would like to protect cells b2 :h3007 & k2:n3007, but it wont run the 'macro buttons that I have created, is it possible to protect sheet but allow the macro buttons to be used?


Thank you.