Conditionally format data in column

[Carpenter]

I have a column of data similar to the following:

A12312-01
A12445-02
W34323-01
A34298-01
W54732-01

and so on.

I am querying all of this from a database with MSQuery.

I need to take all instances that start with "W" and truncate the "01" from the end. If the value does not start with "W", the suffix "0x" must remain.


Thanks for helping a newb!

 

[Joe4]

Off the top of my head, I think you may want to try the Worksheet_Calculate event, which is triggered whenever a cell on the worksheet is calculated/re-calculated.

I think that *should* work, but I really haven't worked much with MSQuery. I think it should trigger that event, but am not certain about that.

 

[Carpenter]

hmm...

no luck there.

I can't seem to get any of the events to trigger anything. I'll keep messing with it.

 

[Joe4]

Make sure that you follow two important rules when using event procedures:

1. They are placed in the correct module. They must be placed in a Sheet module or the ThisWorkbook module. They will not work when placed in a standard module.

2. They must be named as shown. There is no flexibility in naming them.

 

[Carpenter]

This is how I have it set up now. It doesn't seem to do anything....

 

[Joe4]

Hmmm...

Is there anything else happening that we may be able to use as a trigger? How does the whole process happen?

There are other events like "Workbook_Open" and "Worksheet_SelectionChange" that we may be able to use depending on how the whole process happens.

 

[Carpenter]

well... I'll list the steps that the user will take when using this spreadsheet.


1. We open the unpopulated sheet. The Workbook_open() event now calls some code that write to a usage log on the server.... at least thats how i read it.

2. The user then puts an item # value value in cell A3. This is used as the parameter in the SQL.

3. The user then clicks "refresh" on the external data toolbar. This runs the SQL with the new parameter.

note: by default, the spreadsheet opens with the data for item# 0000000, thus creating a blank sheet.

If there were static data in the sheet, i could just do the formatting in worksheet_open(), but since I need to format the data after it is populated, i'm unsure where to go.


Thanks

 

[Joe4]

With all the event procedures, there are many different ways you can go about this. It just probably will involve having to change your process ever so slightly so that one of these events is triggered.

Here is another idea that doesn't use any event procedures:

Instead of having them click the Refresh toolbar button, put the refresh command in a macro, and tack the code we wrote above to the end of this macro. Then attach this macro to a toolbar button or command button, and have them click this button instead of the Refresh toolbar button.

 

[Carpenter]

How do I attach a macro to a toolbar? I put the code into a command button on a user form, but found it to be awkward... Is there a way to put a command button directly on the spreadsheet?

 

[Joe4]

How do I attach a macro to a toolbar?

Create a custom toolbar button, and attach the macro to it.

Is there a way to put a command button directly on the spreadsheet

This is the option I would prefer (personally). Right click anywhere up in the toolbar sections, and select the Forms toolbar. From there, select the Command Button option (looks like a rectangle) and paste it where you want in your spreadsheet. It should automatically ask you what macro you would like to attach to it.

 

[Carpenter]

I figured that this would eventually lead to something that should have been obvious (the button on the spreadsheet via the forms toolbar)... but, you sir, are an intern's best friend

Thanks a bunch!