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
------------
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,331
Messages
5,595,541
Members
413,996
Latest member
mabelO

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