Clicking cells from VBA

[gordonlyle]

Hi clever folks,

Just a very quick query, is it possible to click a cell rather than typing in the cell ref?

Rather than typing in: Range ("A1") I would like to type Range(" and then click the cell in excel to fill in the range details.

I know this is lazy but I have a lot of very small cells in an incredibly complicated workbook and I have to do this A LOT!

Thank you in advance for you brain power!!!

Gordon

 

[Joe4]

You can get the range for any cells you have selected with ""Selection", i.e.

MsgBox Selection.Address

Is that kind of what you are looking for?

I use this in some of my macros when I want to apply the code to whatever range I have selected.

 

[Ramballah]

You can get the range for any cells you have selected with ""Selection", i.e.

MsgBox Selection.Address

Is that kind of what you are looking for?

I use this in some of my macros when I want to apply the code to whatever range I have selected.

i think he means as in, hes making a code and he needs to write alot of code for different cells. so for each cell range he gotta type A43, A44, A45 etc. he just wannts to click on cell A43 and it puts it in his macro.

 

[Joe4]

i think he means as in, hes making a code and he needs to write alot of code for different cells. so for each cell range he gotta type A43, A44, A45 etc. he just wannts to click on cell A43 and it puts it in his macro.

If each succeeding cell follow a certain pattern (i.e. one row at a time), you can just loop through the range, or loop through a row counter.

But before we go down that road, I prefer to let them clarify exactly what they are looking for (and what they are actually trying to accomplish). Making a macro which interacts while it runs and asking people to select different cells in the middle of the macro is not easy (I am not even sure if it can be done). I know you can prompt users for some input, but don't know if you can prompt them to physically select cells.

 

[gordonlyle]

Hi, thanks for the speedy reply.

Sorry, I'm not explaining myself very well and I am fairly new to VBA. The range does not need to change when its being used, I am more after maybe a keyboard shortcut or a ctrl click to use the clicked reference as part of the vba code.

As an example, when in excel work sheets and you are writing a formula, you can either type "=sum(A1+A2)2 or you can type "=sum(" then click on the cell A1 with the mouse, then type"+" and then click on A2 before closing the brackets. I am wondering if there is a way of doing the click on cell approach in Visual basic.



Thanks
Gordon

 

[gordonlyle]

Yes Ramballah, Thats what I mean.

 

[Ramballah]

Yes Ramballah, Thats what I mean.

yeah, im curious if its possible now too because im lazy but i never learned if there is a way for it

 

[Joe4]

As an example, when in excel work sheets and you are writing a formula, you can either type "=sum(A1+A2)2 or you can type "=sum(" then click on the cell A1 with the mouse, then type"+" and then click on A2 before closing the brackets. I am wondering if there is a way of doing the click on cell approach in Visual basic.

No, I don't think that functionality exists in VBA. Though they work together, VBA are Excel are two distinctly different things.

However, there is a Macro Recorder, where if you turn it on, and perform some actions on your Excel sheet, it will automatically convert many of those actions over to VBA code.
So that may assist in VBA code writing.
See here for more details: https://www.contextures.com/excel-macro-record-test.html

 

[gordonlyle]

The main reason for asking (and this is probably something I'm doing wrong) is because I have written loads of macros refering to cells ranges and subsequently moved these cells and altered the layout of the work sheets around to look more pleasing / functional and although the cell references in formulas will update, the cell ranges in VBA do not seem to.

 

[Joe4]

The main reason for asking (and this is probably something I'm doing wrong) is because I have written loads of macros refering to cells ranges and subsequently moved these cells and altered the layout of the work sheets around to look more pleasing / functional and although the cell references in formulas will update, the cell ranges in VBA do not seem to.

That is expected behavior. As I said, Excel and VBA are different programs, so changes that you make to your Excel sheet will have no impact on your VBA code.

If you do that a lot, here is what I recommend doing. Use Range variables, and define your ranges at the top of your code, then in the body of your code, write it against the range variable names and not hard-coded ranges. Then, if you change the layout of your sheet, you would only need to change the range reference values at the top of your code.

For example, let's say that we have a Title row that we want to bold and underline. That (very simplified) section of code may look something like this:

Sub Test()

'   Set title range
    Dim rngTitle As Range
[COLOR=#ff0000]    Set rngTitle = Range("A1:C1")[/COLOR]
    
'   Write code against range to bold and underline
    With rngTitle
        .Font.Bold = True
        .Font.Underline = xlUnderlineStyleSingle
    End With
    
End Sub

And maybe you will be doing other things to this Title row too, so you might have more code below that. If you were to ever change the title row, you would then just need to update that one line in red at the top, instead of a whole bunch of hard-coded range references down in your code.

Also, if you have some sections of code dependent on other selections, you may be able to use things like Offset and CurrentRegion to move relative to another range reference, instead of hard-coding those references. You can Google those for more details.