vba macro: retrieve cell data wihtout selecting cell?

haw

Board Regular
Joined
Jan 18, 2005
Messages
113
Excel 2000 SP3 (if it matters).

I need vba code to retrieve the text data from cells in a DIFFERENT sheet than the one containing the controls that fires the macro.

I've been using the code below ... which works fine when the macro is fired from the same sheet as the range I'm referencing. But it won't work if the range is in a different sheet.
ActiveSheet.Shapes(rowindex).Select
Selection.Characters.Caption = Range("ServiceNames").Cells(rowindex, 5).FormulaR1C1

I've tried adding changing "ActiveSheet" to "Sheets("mysheetname")", and a couple of other things ... but without success. I need to access the data as a Range because I'm "stepping through" the range using offsets from R1C1 in the range.

And one other thing, while we're here ... is there any way for me to combine the two lines above into only one ... so that I don't actually select the Shape prior to changing the Caption.

Thanks in advance!
Adam
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

CraigM

Active Member
Joined
Feb 27, 2003
Messages
320
I think you want something like this:

Sheets("Sheet1").Shapes(RowIndex).TextFrame.Characters.Caption = Sheets("Sheet1").Range("ServiceNames").Cells(RowIndex, 5).FormulaR1C1

You'll need to change the two instances of "Sheet1" to your appropriate sheet names.
 

haw

Board Regular
Joined
Jan 18, 2005
Messages
113
Excellent! Just what I needed. Thank you.

This sparks another question from me:
I spent several hours yesterday trying to figure this out on my own -- recording macros & examining resulting code, searching various VBA forums on the web looking for examples, reading what I could find, reading Excel/VBA help, etc. But I was never able to find the info I needed. I knew it was a simple matter of what I call syntax, but still could not find the right reference. Is there a good book or online reference source that will describe for me the various properties and methods for Excel Form controls? I really hate asking questions in a forum if I can just RTFM instead.
 

CraigM

Active Member
Joined
Feb 27, 2003
Messages
320
Have you used the object browser? View>Object Browser in the VBE. It is often a good place to start, as it gives you a list of all properties, methods and events for a given object.
 

haw

Board Regular
Joined
Jan 18, 2005
Messages
113
The object browser will go a long way towards answering my "syntax" questions. I'm having one bit of trouble, though. As an example, right now I'm trying to figure out how to change the .linkedcell property for a (Excel Forms) checkbox, without first selecting the control. I can see from the code generated by a macro that when I "manually" change the .linkedcell property, the generated code first selects the checkbox and then assigns a cell reference to the .linkedcell property. Fair enough. But I want to do this without first selecting the control. And I cannot find a "checkbox" or "xlcheckbox" method, control, or property in the Object Browser. Well ... I take it back ... I DO find that xlcheckbox is the first value of an enumerated data type (used to index Shapes. But I cannot find the a list of properties that includes ".linkedcell" for any object. I guess my question is (for this example), how/where do I look to find the keyword that I must put in front of ".linkedcell" to make my code work? Sheets("mysheet").Shapes(myindex).linkedcell does not work, even though it looks to me like it should. I'm having this same problem trying to access/modify the values of several other things (be they cells in a sheet, controls on a form, or whatever).
 

CraigM

Active Member
Joined
Feb 27, 2003
Messages
320
I admit it can sometimes be frustrating, as things are not always as obvious as it seems they should be. I don't know of any 'full' reference material, but it may well exist. Usually, I find that between the object browser, help, and this forum I can find what I need.

If you search for "LinkedCell" in the OB or Help, you'll probably find some reference to the ControlFormat object. I didn't know it existed either, but this seems to work:

ActiveSheet.Shapes(1).ControlFormat.LinkedCell = "A1"
 

Forum statistics

Threads
1,147,681
Messages
5,742,597
Members
423,740
Latest member
JCF_

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