NeedsHelp2
New Member
- Joined
- Nov 20, 2005
- Messages
- 22
Over the past week or so I've been creating a spreadsheet with various userforms and approximatly 6 worksheets.
Everything seems to have been working fine until I added the code below to an option button. As soon as I added the code one of the userforms took 20 mins to open.
Thankfully I do regular backups so was able to go back to the last version.
I added the same piece of code and once again the same userform seemed to give up on me. I did not even try to get the code below to run and am at a loss to understand why the userform is acting this way.
Is anyone able to shed any light on this?
Everything seems to have been working fine until I added the code below to an option button. As soon as I added the code one of the userforms took 20 mins to open.
Thankfully I do regular backups so was able to go back to the last version.
I added the same piece of code and once again the same userform seemed to give up on me. I did not even try to get the code below to run and am at a loss to understand why the userform is acting this way.
Is anyone able to shed any light on this?
Code:
Private Sub OptionButton15_Click()
Dim SourceBook As Workbook
Dim FileToOpen As Variant
Dim ans
Dim LastRow As Long
Dim CopyRange As Range
If MsgBox("This will update all LookUp Tables, do you REALLY want to continue?", vbOKCancel) = vbCancel Then
Cancel = True
Exit Sub
Else
Unload Me
Sheets("LookUps").Select
Rows("2:65536").Select
Selection.Delete Shift:=xlUp
On Error GoTo Errorhandler:
Set SourceBook = ActiveWorkbook
Application.ScreenUpdating = False
FileToOpen = Application.GetOpenFilename("XLS Files (*.xls), *.xls", , "Open a xls file")
If FileToOpen = False Then
MsgBox "stopped by user"
Else
ans = MsgBox("The following file will now open -" & vbCr & _
FileToOpen & ". Is this correct?", vbYesNo + vbDefaultButton2)
If ans = vbYes Then
Workbooks.Open FileToOpen
'------------------------------
LastRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
'------------------------------
LastCol = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
'------------------------------
Set CopyRange = ActiveSheet.Range(Cells(1, 1), Cells(LastRow, LastCol))
CopyRange.Copy Destination:=SourceBook.Sheets("LookUps").Range("A1")
ActiveWindow.Close
Else
Exit Sub
Errorhandler:
MsgBox ("Selection cancelled")
End If
End If
End If
Application.ScreenUpdating = True
MsgBox "Completed!"
End Sub