0

I'm running macros in one workbook, and after each run I need it to go to an inactive workbook and highlight the active cell (or, the cell that was active before I went to another workbook). Here's what I'm trying:

myWorkbook.Sheets("mySheet").Activate
Worksheets("mySheet").ActiveCell.Interior.ColorIndex = 8

I keep getting an error from the second line saying the object doesn't support this property or method. The sheet is open, but not currently the sheet I'm in when this code runs.

If possible, can I fix this and also have it move the cursor/selection down to the next cell afterward?

Thanks!

Edit: I've also tried myWorkbook.Sheets("mySheet").ActiveCell.Interior.ColorIndex = 8

litelite
  • 2,857
  • 4
  • 23
  • 33
pez
  • 3,859
  • 12
  • 40
  • 72
  • Did you try `myWorkbook.Worksheets("mySheet").Cells(1,1).Interior.ColorIndex = 8`? (where you'll have to adjust (1,1) to the correct address). – Ralph Jun 27 '16 at 13:42
  • @Ralph Yes, I tried that earlier. Sorry, I just posted the most recent version I tried. I also made sure the workbook is macro-enabled, but that didn't fix it. – pez Jun 27 '16 at 13:43

2 Answers2

1

You're using ActiveCell, which I recommend not doing. Avoid using .Select/.Activate

If you're trying to highlight the active cell, you need to put the macro in a worksheet module:

Private Sub Worksheet_SelectionChange(ByVal target As Range)
    Target.Interior.ColorIndex = 8
End Sub

As pointed out, in your code, you don't ever choose a cell, so you're getting the error.

Also, you'll note that this method keeps all other cells highlighted. If you have NO OTHER CELLS that need to be highlighted, add cells.interior.colorindex = 0 right before the one line that's there. Also, here's a thread that addresses this. You can do what you're attempting without VBA.

Community
  • 1
  • 1
BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • Can I run this from a different workbook still? The sheet I'm trying to highlight is in a different workbook. Can you edit to add a parameter for the workbook to highlight? – pez Jun 27 '16 at 13:47
  • @pez - In your macro, do you know which cell in the new workbook you are going to highlight? That would make it easier. Is there any code that's going to call this one? – BruceWayne Jun 27 '16 at 13:49
  • Yes. The sheet I'm trying to highlight is basically a list of about 1,000 numbers in column A. I highlight one at a time as a process each one. So, if I've processed the first 99 so far, the last cell I selected with my mouse/cursor (without VBA) was cell 100. That's the cell I want to highlight - the first 99 are already highlighted. The next will be 101, etc. I'd rather try to write it to highlight the cell that was last selected by me, rather than hardcode a number, if possible. – pez Jun 27 '16 at 13:51
  • @pez - Can you post the code that's going to call the cell to highlight? – BruceWayne Jun 27 '16 at 13:56
  • Apologies, I'm new to VBA and the above code is all I had tried. I didn't know I the call `Sheets("mySheet").ActiveCell` was the incorrect part, so I didn't try calling a specific cell. – pez Jun 27 '16 at 14:00
0

The active cell object doesn't belong to the worksheet object. This is because there is only one active cell object, not one per sheet.

Once you have made MySheet active you can directly call on ActiveCell like so:

Working Example

myWorkbook.Sheets("mySheet").Activate
ActiveCell.Interior.ColorIndex = 8

I agree with @BruceWayne. Avoid calling Select and Activate if you can.

David Rushton
  • 4,915
  • 1
  • 17
  • 31
  • What are the drawbacks to calling `Activate`? Can I highlight the cell in an inactive workbook without calling it? – pez Jun 27 '16 at 13:53
  • 1
    @pez - As you've seen, you have to actually `activate` a cell, to use `Activate`. A much better way is to instead deal directly with the data. If you know your `ActiveCell` is going to be `C1`, then it's better to use `Range("C1").Interior.ColorIndex = ...` Also, what the `ActiveCell` or `ActiveSheet` is isn't always what you may think, so it's better to avoid. – BruceWayne Jun 27 '16 at 13:55
  • 1
    There is a [link](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) in @BruceWayne's answer that highlights some of the draw backs. One issue is you cannot stop the user from moving the focus while your code runs. In general it's best to be explicit. Code that selects the correct cell, given some criteria, is more robust that checking the state of the UI. – David Rushton Jun 27 '16 at 13:56