Repeatable Macro

[smoscar]

Hello, I would like to know how to record the following macro to repeat until it reaches a blank cell: Double click in a cell and then hit enter.

I am not familiar with VBA, and have tried to use the Relative Reference button while recording.

I would really appreciate any help, thanks!

[DRJ]

What do you want to do to the cells? Double click and then enter?

[Cbrine]

This code should cycle through each cell in the range A1 to the last data value in column A. If it finds a blank cell, it will end.

sub Repeat()
Dim R as range

for each R in activesheet("A1",range("A65000").end(xlup))
If r.value ="" then
Exit loop
End If


Your Code Here
Next
End Sub

[smoscar]

Hi Jacob,
I'm not sure how to respond to you on the message board, but yes, I just want to double click on a cell with a value, and then press enter. Excel requires this to be done when changing the formatting of values.

Regards,
Steve.

[DRJ]

You can use application.doubleclick to send a double click, but if you just want to reformat the cell you can do that without a doubleclick

i.e.

range("A1").Value = Format(Range("A1").Value,"0.00")

So this is just depending on what you specifically want to do.

[Tom Urtis]

Originally Posted by DRJ

You can use application.doubleclick to send a double click

What do you mean by that? Can you give an example?

[DRJ]

Originally Posted by Tom Urtis

What do you mean by that? Can you give an example?

OP wanted to double click. I asked what he really wanted to do, but to send a doubleclick you can use application.doubleclick. Or am I missing something?

From Excel Help

DoubleClick Method
See Also Applies To Example Specifics
Equivalent to double-clicking the active cell.

expression.DoubleClick

expression Required. An expression that returns an Application object.

Example
This example double-clicks the active cell on Sheet1.

Worksheets("Sheet1").Activate
Application.DoubleClick

[Tom Urtis]

And did you test it? Put this in the Sheet1 module:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
MsgBox "Hello"
End Sub

Doubleclick in Sheet1, and you will see the message box, with the cursor flashing inside the cell you double clicked because now you are in Edit mode.


Go to Sheet2 and run the macro you just posted.

Do you see the message box pop up now? Do you see the cursor flashing in a cell as if you doubleclicked anything?

[DRJ]

So it doesn't trigger the sheet event. It still sends a double click, though excel probably ignores it if the object is a cell. I played with it a bit, and if the onject is a chart it works.

Anyways I gave the OP two possible solutions to try, if neither of those work I am sure he will post back and someone else can help.

[erik.van.geit]

smoscar,
you asked at the start

"I would like to know how to record the following macro to repeat until it reaches a blank cell: Double click in a cell and then hit enter. "

further on

I just want to double click on a cell with a value, and then press enter. Excel requires this to be done when changing the formatting of values

Excel doesn't require doubleclicking to change format : selecting is enough (in VBA you don't have to select as you can see in the formula from DRJ)
Guys experts, are we not losing focus here ?
or do I really miss something here, then apologise

smoscar,
do you want to format a given range just once or do you need a macro to format several times ?
which range would you like to format ?

just hoping to help in a simple way
regards,
Erik