Excel VBA - forms

[James Wilkinson]

Hi

I have a problem with a VBA form I've created in Excel. I have set u
an OK button which, when clicked, takes the information that has bee
inputted on the form and puts it on to a worksheet.

Here is a sample of the code:

Range("c1").select
ActiveCell.Offset(0, 1) = txtCountry.Value
ActiveCell.Offset(0, 2) = txtRegion.Value
ActiveCell.Offset(0, 3) = txtStreet.Value
ActiveCell.Offset(0, 4) = txtName.Value

where txtCountry etc are the fields on the form.

When I run this macro, it goes incredibly slowly, although correctly
Although it is a reasonably large spreadsheet, I do have a fas
computer and I would not expect this. It is taking about half a secon
to add each item.

Any ideas as to why this is happening would be greatly appreciated.

Thanks
Jame

 

[papou]

Hello James
Not too sure but selection of a range in your code may be the reason for a
slow execution, I would suggest you use something like:
With Worksheets("Feuil1")
..Cells(1, 4).Value = Me.txtcountry
..Cells(1, 5).Value = Me.txtregion
..Cells(1, 6).Value = Me.txtStreet
..Cells(1, 7).Value = Me.txtName
End With

HTH
Regards
Pascal

 

[Bob Phillips]

Hi James,

Not enough info as to why it is so slow, but you could try this to speed it
up.

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

With Range("c1")
.Offset(0, 1) = txtCountry.Value
.Offset(0, 2) = txtRegion.Value
.Offset(0, 3) = txtStreet.Value
.Offset(0, 4) = txtName.Value
End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)