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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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