Why Is This Happening?

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?

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
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
I see several things that could be the culprit. The best thing to do is to try stepping through the code one line at a time and just see where it bogs. To do this just press F8 in the VBE.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,069
Office Version
  1. 365
Platform
  1. Windows
Do you really want to delete 65535 rows here?
Code:
Sheets("LookUps").Select 
Rows("2:65536").Select 
   Selection.Delete Shift:=xlUp
Does this work any better?
Code:
Sheets("LookUps").Rows("2:65536").ClearContents
 

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
Almost brought that up myself :LOL: Slowdown could be where the workbook is being opened too.
 

NeedsHelp2

New Member
Joined
Nov 20, 2005
Messages
22
Norie, had already tried yoyr suggestion but made no difference.

Just to clarify, the userform that is being slowed down has nothing to do with the code that seems to be affecting it?
 

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
You need to step through the code that is being slowed down.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,002
Messages
5,575,488
Members
412,669
Latest member
nickyon1
Top