How to repeat and end macro

Tanda Badal

New Member
Joined
Oct 24, 2002
Messages
8
Hi


I have the following two macro coneccted (123456 is a form title). and I am trying to fill three cell in a row and than go to next row and repeat but with new data. When pressing on vbOK, I would like to cursor to go to TxtBORG. And when vbCancel it terminate the Macro. At the moment I have to presess the Cancel bottom quite few times before it will terminate the macro.

Please can you look at this and tell me how I can achieve this.

Many thanks for you help. Please see below for the Macro.

Sub Testing31Oct2002()

With 123456

.txtBORG = ""
.TxtNominal = ""
Formula = "=VLOOKUP(" & (ActiveCell.Address) & ",'Full Client List'!B5:C3049,2,0)"

.Show

If .Tag = vbCancel Then Exit Sub

If .Tag = vbOK Then
ActiveCell = .txtBORG
ActiveCell.Offset(0, 1).Select
ActiveCell = Formula
ActiveCell.Offset(0, 1).Select
ActiveCell = .TxtNominal
ActiveCell.Offset(1, -2).Select

Exit Sub


End If


End With

End Sub

----------------

Sub Bismillah()

[a10].Select
If vbCancel Then EndBismillah


Testing31Oct2002
Testing31Oct2002
Testing31Oct2002
Testing31Oct2002
Testing31Oct2002

End Sub
------------
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Rather than using the Tag property try putting your code in the Click event procedures for the OK and Cancel buttons, like this:

Code:
'OK Button
Private Sub CommandButton1_Click()
   Formula = "=VLOOKUP(" & (ActiveCell.Address) & ",'Full Client List'!B5:C3049,2,0)" 
   ActiveCell = txtBORG 
   ActiveCell.Offset(0, 1).Select 
   ActiveCell = Formula 
   ActiveCell.Offset(0, 1).Select 
   ActiveCell = TxtNominal 
   ActiveCell.Offset(1, -2).Select
End Sub

'Cancel Button
Private Sub CommandButton2_Click()
   Unload Me
End Sub
This message was edited by Andrew Poulsom on 2002-11-01 06:16
 

Tanda Badal

New Member
Joined
Oct 24, 2002
Messages
8
Hi Andrew,

That was great it worked, thank you. Please can you also advise how I can make it that when I click on Okay after entering the data that the cursor moves back to first field on the form, where the user has to enter first field (txtBORG).

Many thanks.
 

Tanda Badal

New Member
Joined
Oct 24, 2002
Messages
8

ADVERTISEMENT

Thats great Thank you.
 

Tanda Badal

New Member
Joined
Oct 24, 2002
Messages
8
Hi Andrew,

Please advise, If I want the form to be empty after pressing Okay, what command do I enter?

Have tried few commands but it just simply closes the form

Once again Many thanks for your help.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

Just put:

txtBORG = ""
TxtNominal = ""

before End Sub in the OK button click event procedure.
 

Tanda Badal

New Member
Joined
Oct 24, 2002
Messages
8
Hi Again,

Each time I want to run the macro a fresh I would like the existing data to be deleted. But just the text, and the format remains.

Please can you help. I have tired various.

[A10:F60].clear

But this clear everything.

Thanks once again.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,139
Messages
5,835,627
Members
430,372
Latest member
contentment

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top