vba macro: retrieve cell data wihtout selecting cell?

[haw]

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

 

[CraigM]

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]

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]

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]

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]

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"