Cells becoming 'unselected' when window focus shifts

tcurrier

Board Regular
Joined
Apr 27, 2006
Messages
175
I am writing a little 'Excel Helper' application in Visual Basic, that runs 'macros' against the currently open Excel Worksheet.

I would like to run some code against the currently selected range of cells in the worksheet, but when I click on the VB button that runs the code, the focus shifts from the worksheet to the VB app, and the cells in the worksheet are no longer selected.

I know this is probably not a question that's come up before, but does anyone have any idea how to retain the 'selected' status of the cells in the worksheet ?

Thanks.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Why do you need to retain the selection?

Most things are possible without activating/selecting, even when you automate Excel.
 
Upvote 0
Because my VB code is attempting to run code against the currently selected range of cells. Once I click the VB button, it appears that the the cells are no longer selected, and therefore the VB code doesn't recognize the previously selected range of cells.
 
Upvote 0
Well don't try to run the code against the selection.

Like I said there is no need for selection/activation.

And if you need to work with the selection you can easily create a reference to it right at the start of the code.
 
Upvote 0
Huh??? That was the purpose of my post, to find out how I could run code against the 'selected' cells.... I don't want to run it against all of the cells in the spreadsheet.

Anyway, I went back and tried it again... I don't know what changed, but now it seems to be working.....

Thanks anyway.
 
Upvote 0
Well what I mean is that you could reference the selected cells something like this.
Code:
Set rng = Selection
Now you can use rng to refer to those cells.

Without seeing your actual code, or knowing it's purpose, it's hard to illustrate further.:)
 
Upvote 0
Thanks, I'll keep that in mind.... I realized later than my VB app wasn't closing down the instance of the Excel app when I closed it, so maybe that's why the selection wasn't working. (I had over 8 instances of the Excel workbook open at the same time)

My only 'purpose' was to be able to do calculations on only the cells that were selected. I didn't have any particular 'code' in mind, just that I wanted to be able to code it so that whatever cells the user selected, the code would be executed only against those cells.

I'm just developing this to help out my co-workers so they don't have to run macros or develop formulas to do their analysis. I thought a nice little VB window popping up would be a handy 'interface' to allow them to perform various functions against the data in the spreadsheet.

Actually the VB window pops up first, then I invoke Excel via OLE automation.

Code:
Dim xlApp As New Excel.Application
Dim xlWorkbook As Excel.Workbook
Set xlWorkbook = xlApp.Workbooks.Open("c:\temp\get_data.xls")

If anyone has any ideas or suggestions for my new app, I'd be glad to hear about them..

Thanks.
 
Upvote 0
Seems a bit like overkill to be using VB for calculating in Excel.

But then again I've no idea what the calculations are or how exactly you are using VB.
 
Upvote 0
Just simple calculations like counting the number of cells that meet a certain criteria, adding up the numeric value of a range of cells, finding the average value of a range, etc.

I know this can be done using formulas within Excel, but I thought I'd make it even simpler by popping up a VB window with some buttons on it that say 'add' , 'count', 'average', etc.

I know this could probably be done in Excel/VBA using 'userforms', but how would I apply this same userform to every excel worksheet that I wrote and distributed ?
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

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