MrExcel Publishing
Your One Stop for Excel Tips & Solutions

help speed up code (the code copies info from userform to sheet)


Posted by Steve W on May 20, 2001 4:12 PM

Does anyone know how I can speed this up. Now it takes 30 seconds to run, thats to slow for me. I use basicly the same concept on a userform activate to load the form and it happens in seconds.
Is there a better way it didn't seem to work on a userform deactivate.
Thanks for any help
steve w


Private Sub CommandButton3_Click()
Sheet7.Range("c1") = RefEdit1.Value
Sheet7.Range("c2") = RefEdit2.Value
Sheet7.Range("c3") = RefEdit3.Value
Sheet7.Range("c4") = RefEdit4.Value
Sheet7.Range("c5") = RefEdit5.Value
Sheet7.Range("c6") = RefEdit6.Value
Sheet7.Range("c7") = RefEdit7.Value
Sheet7.Range("c8") = RefEdit8.Value
Sheet7.Range("f1") = RefEdit9.Value
Sheet7.Range("f2") = RefEdit10.Value
Sheet7.Range("f3") = RefEdit11.Value
Sheet7.Range("f4") = RefEdit12.Value
Sheet7.Range("f5") = RefEdit13.Value
Sheet7.Range("f6") = RefEdit14.Value
Sheet7.Range("f7") = RefEdit15.Value
Sheet7.Range("f8") = RefEdit16.Value
Sheet7.Range("I1") = RefEdit17.Value
Sheet7.Range("I2") = RefEdit18.Value
Sheet7.Range("I3") = RefEdit19.Value
Sheet7.Range("I4") = RefEdit20.Value
Sheet7.Range("I5") = RefEdit21.Value
Sheet7.Range("I6") = RefEdit22.Value
Sheet7.Range("I7") = RefEdit23.Value
Sheet7.Range("I8") = RefEdit24.Value
End Sub


Posted by Dave Hawley on May 20, 2001 4:35 PM

Hi Steve

It shouldn't take that long. I'm guessing you have cells referencing the changing cells, so try this.


Private Sub CommandButton3_Click()
Dim i As Integer

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

With Sheet7

For i = 1 To 8
.Cells(i, 3) = RefEdit & i
Next i
For i = 1 To 8
.Cells(i, 6) = RefEdit & i + 8
Next i
For i = 1 To 8
.Cells(i, 9) = RefEdit & i + 16
Next i

End With

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

DaveOzGrid Business Applications

Posted by Dave Hawley on May 20, 2001 4:45 PM

Ignore the one directly above here. It wont work

Private Sub CommandButton3_Click()
Dim i As Integer

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With sheet7
.Range("c1") = RefEdit1.Value
.Range("c2") = RefEdit2.Value
.Range("c3") = RefEdit3.Value
.Range("c4") = RefEdit4.Value
.Range("c5") = RefEdit5.Value
.Range("c6") = RefEdit6.Value
.Range("c7") = RefEdit7.Value
.Range("c8") = RefEdit8.Value
.Range("f1") = RefEdit9.Value
.Range("f2") = RefEdit10.Value
.Range("f3") = RefEdit11.Value
.Range("f4") = RefEdit12.Value
.Range("f5") = RefEdit13.Value
.Range("f6") = RefEdit14.Value
.Range("f7") = RefEdit15.Value
.Range("f8") = RefEdit16.Value
.Range("I1") = RefEdit17.Value
.Range("I2") = RefEdit18.Value
.Range("I3") = RefEdit19.Value
.Range("I4") = RefEdit20.Value
.Range("I5") = RefEdit21.Value
.Range("I6") = RefEdit22.Value
.Range("I7") = RefEdit23.Value
.Range("I8") = RefEdit24.Value
End With

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub


Dave

OzGrid Business Applications

Posted by steve w on May 20, 2001 7:26 PM

Thanks dave its still slow but faster(Quick questine)

Thanks dave
Am I going about unloading my form the right way or is there a better way.

Thanks once again for the help
steve w

Posted by Dave Hawley on May 21, 2001 3:15 AM

Re: Thanks dave its still slow but faster(Quick questine)

Hi Steve

I don't know how you are unloading your UserForm now Steve ? But the only way to unload a form is with the keyword "Unload". I always use:

Unload Me

I'm curious why you have so many Refedits on one UserForm though and what you are using them for ? there purpose is supposed to be to collect a Range Address as a String and either use it like:

Range(Refedit1) or
Set rRange = Range(Refedit1)

From what I can gather you are passing a String range address to other cells ?

If I remember correctly you are making a Wizard ? Drop me an email and I'll post a helpful link.

Dave

OzGrid Business Applications