Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Microsoft Excel _ Code Fires When Worksheet Activates - Not A "form" Issue

Posted by: bakersburg9 Dec 7 2017, 04:32 PM

I am working with 2 workbooks, and one has two tabs (worksheets) - I want my code (which is just selecting a single cell range) to run when I activate the worksheet - If I have to hard-code it, that's great, because the tab names don't change - everything I found when I searched the 'net is for FORMS in Excel - I found this, and tweeked it, but no go...

CODE
Private Sub Worksheet_Activate()
ComboBox1.Activate
End Sub
I tried replacing the combo box reference, but it didn't work - like I said, if I have to hard-code the reference to the particular workbook, that would be even better...

... any help would be greatly appreciated . . .

Posted by: doctor9 Dec 7 2017, 04:41 PM

bakersburg9,

> which is just selecting a single cell range
> ComboBox1.Activate

The line of code seem to contradict your description, unless you have named the cell "ComboBox1" for some reason.

The worksheet activate code will not fire if you have the Design Mode toggle on the Developer tab active. Is it possible that this is why your code is not firing?

If it's not a case of the code not firing, can you clarify "no go" and "didn't work"? Are you getting an error message? If so, what is the error? Also, if this is just the code you found and tweaked, can you post your tweaked code that isn't working?

Hope this helps,

Dennis

Posted by: bakersburg9 Dec 7 2017, 05:05 PM

Dennis,

My "tweek" was simply replacing

CODE
ComboBox1.Activate
with:
CODE
Range("AV29").Select

...when I said "no go," I meant it didn't work - there was no error message - it simply did not produce the desired result ...
not sure about the Design mode toggle part - this was not something I was attempting to run via the code window, or a keyboard s/c, or a button on the QAT - I just thought when I activated the worksheet, it would fire... similar to a got_Focus event in MS Access

I simply want to go to a certain cell - this may be better with a hard-coded reference to a specific worksheet - but... baby steps - I want it to work before I do that - but that would be better, because I only want my code to run when I go into one particular worksheet

Posted by: doctor9 Dec 7 2017, 05:49 PM

bakersburg9,

Okay, it seems like the code isn't firing.

The Design Mode toggle is often used when doing things like adding or editing ActiveX controls. It prevents code from firing while you select controls.

Have you placed a breakpoint on the sheet's activation code to verify that it's really not firing? If so, maybe you could attach the file and we could see what's going on.

Hope this helps,

Dennis

Posted by: bakersburg9 Dec 7 2017, 11:11 PM

I found this on Stack Overflow:

QUOTE
You can put certain code in the Worksheet_Activate() function which will run when the sheet is selected. Additionally, use the Worksheet_Deactivate() to run code when you leave the worksheet and go to another one. These functions go in the worksheet object code

Wow. I’m drooling over this – wowza – this is GOLD! (If I can get it to work)

Here is the code sample they provided:
CODE
Private Sub Worksheet_Activate()
MsgBox (“Hi”)
End Sub


But try as I may, doesn’t work – see attached…


 ActivateWorksheet.zip ( 18.63K ): 4
 

Posted by: BuzyG Dec 8 2017, 04:36 AM

Where are you placing the code?

It needs to be in the sheet's module. Not the workbook's module.

Posted by: doctor9 Dec 8 2017, 09:14 AM

bakersburg9,

You have placed the code in a public code module that isn't attached to any of the worksheets. That's where you would place generic code that can be called from anywhere. If you want to add code to a specific worksheet, the easiest way to do it is to right-click the tab and select "View Code". Select "Worksheet" from the combobox in the upper left, and "Activate" from the combobox in the upper right.

BuzyG must be telepathic - time to buy a lotto ticket!

Hope this helps,

Dennis

Posted by: bakersburg9 Dec 8 2017, 11:20 AM

Thanks, Dennis and Buzy - it works now, but I'm going to have to train myself on this - don't see the advantage, but I'm new to this putting code in a worksheet - THANKS !! cool.gif

Posted by: doctor9 Dec 8 2017, 11:25 AM

bakersburg9,

There's basically three different places you can put code in an Excel file:

1. In a standalone code module. This is for things like user defined functions and functions that every worksheet could potentially use.
2. In a worksheet code module. This is for stuff that's specific to that one worksheet.
3. In the workbook code module. This is where you'd take advantages of file-specific events like the workbook's Open event, or the SheetChange event, or the BeforePrint event.

Hope this helps,

Dennis

Posted by: bakersburg9 Dec 9 2017, 12:22 AM

Dennis,
I'm going to experiment with those - here's what this all was for - our company was creating new invoices for about 250 of our customers - with all the macros I wrote, and my knowledge of keyboard shortcuts, I was able to crank out more than 3 every two minutes- without all that, it would take about 2 minutes to complete one - at least 2 minutes.

That being said, I'm sure a bunch of code could be written to, once in place, knock this project out in an hour or two.

So you have the "old" invoice, and the new invoice - the name of the excel workbooks are a concatenation of the date MMDDYY, the street number and street name of the business location. So an invoice (created in Excel) for a job set up at a company located at 3560 Main street on november 15th, 2017 would be INV111517MAIN.xlsx

Basically, all the values that needed to be copied from one invoice (the "old" one) to the new invoice could be found in the same location as far as cell references. In most cases, you couldn't just copy/paste/special values from one cell range to another, because of either formatting or locked cells. You had to take your mouse pointer to the edit window and highlight the contents, and THEN go to the destination cell, and paste it there. But once you got to the destination in the new invoice you had to hit the backspace key before pasting. What my macros did was move from location to location, and copy ranges of values to the buffer in cases where you could actually get away with that ....

So I would use a workbook in the new format as a template - then open an old-style invoice, press F12 and copy the name to my buffer, then toggle to the new format workbook, paste the name that was to be used (the Invoice number) and add an "a" at the end, since Excel doesn't allow 2 workbooks with the same name opened at once. Then I would copy all the data over, and save the new workbook. When done, I removed all the filenames with the "a" - I'm sure they were impressed I got it done in a fraction of the time they were expecting, but I'm sure there is a MUCH faster way I could've done it - especially since all the different pieces of data were going from the same cells in one to the same location in the other (the "new" one)

Like I said, I'm sure there would've been a much faster way of doing it - any suggestions ?