Posts by Barbarr

Re: Force Consecutive Open for Multi-Selected Files OR Detect Multiple Files Opening

Quote from Andy Pope

Not sure how easily this approach would be to build into your system, especially as it requires a rethink on the code within the template file.
Rather than firing the addin routine and using the activeworkbook object pass the workbook object as an argument.
Template file Open event[vba]Private Sub Workbook_Open()
Application.Run "Main.xls!Opening", Me
End Sub[/vba]Addin routine[vba]Public Sub Opening(Book As Workbook)
MsgBox "Opening workbook " & Book.Name
End Sub[/vba]
In my test when opening 3 workbooks at once all 3 are opened when the addin routine is executed for the first time. You could check for all open workbooks and store information about whether the workbook has been processed or not.

Display More

Thanks Andy. Yes, I have given thought to both these suggestions but thought I might troll for some alternatives, particularly if Excel/VBA could detect whether it was being asked to open a single file or multiple files.

I have left the project open on these files so that I can add/revise code programatically (there is minimal programming within each workbook). I've been following the threads re adding VBA code programmatically but have been avoiding trying it. So changes to each workbooks Workbook_Open event would require that I change the code programmatically. I'll play with this suggestion to see if it works but I would still need to loop through the open files the first time to ensure the change has been made.

My easiest solution will be to modify the code in the add-in to loop through the open workbooks (and I need it anyway if your first suggestion works). Fortunately, each revision has a check built-in that can be used to determine if the workbook has been processed to ensure that the code isn't run again when the workbook is opened after successful updating and the files are unique enough that I can have the loop skip workbooks that aren't based on my template.

Not sure if anyone has any other suggestions but I think I'll use these. Still if anyone knows if Excel/VBA can detect multiple files being opened I would be interested to know how.

Thanks all!

Re: Force Consecutive Open for Multi-Selected Files OR Detect Multiple Files Opening

Quote from xlite

in the add-in list all the filenames to be opened (hope not many)

Not feasible since the files are created from a template and the users name the files. I have no idea how many files or the filenames and the files and names change from one customer to another. But thanks for the thoughts.

Re: Force Consecutive Open for Multi-Selected Files OR Detect Multiple Files Opening

Quote from maxihoya

If so, why not have the code reside in each workbook...?

Because, the files are created from a template given to many clients from which the client's users create many files. Since these are remote clients, and for security reasons and for code updating/revisions, the code is kept in the add-in -- you know, one file to revise insteads of hundreds for which I have no idea the filenames. But thanks for the thoughts.

Re: Force Consecutive Open for Multi-Selected Files

Thanks Maxi but ThisWorkbook will not work because other than the Workbook_Open in the ThisWorkbook module of each file, the rest of the code is in the add-in.

I need Excel/VBA to detect when multiple files are being opened (if that is possible). And then, preferably, to suspend opening the other files until all the code associated with the Workbook_Open event has run for the first file, etc.

I had thought of the variable but again that would require that VBA/Excel can detect that multiple files are being opened and assign the name of each file being opened to an array. I could then use the array to loop through the opened files.

I have an application that is a template. From the template, several files are created. A separate add-in provides the bulk of the programming for the template and each file created from the template.

I frequently need to update the add-in and distribute it to my clients. Each time some change is made to the client-created files even if it's just stamping the new add-in version number and date on a very hidden sheet.

The template (and files created from the template) have a Workbook_Open procedure that runs codes from the add-in, including any updating chores.

If a user opens a file and then goes back and opens another file, the updating code is run on each of the files (both the first and second file).

My problem occurs when the user multi-selects files to open. All the files seem to open first, and then the Workbook_Open for each file is triggered; however, since the add-in assumes the activeworkbook, then the last file opened is the only one that gets updated.

Is there anyway through VBA to force the files to be opened consecutively instead of concurrently so that the Workbook_Open code (and associated code) is run before the next file is opened?

Re: Vlookup function

You're welcome Fatimah. I noticed that while I was composing a response to you that Bob answered you. I just gave you your file back with Bob's formula.

You did very well posting a file with your very first post. You just need to remember next time to either tell us or provide a spot where you want the formula put. Just another little piece of the picture to help us help you.

You may not have noticed but in Bob's first post (#2) of this thread, Dave Hawley has edited Bob's post to add a link for VLOOKUP. If you click on that link, you'll be taken to an article that Dave has written that may help you to better understand the VLOOKUP function and how to use it.

Re: Copy & Paste in blank cells between values

Hi and welcome to OzGrid, 3Sixty.

A Friendly Reminder for a new member!

I have added code tags for you in this post. Please read the rules you agreed to particularly concerning Code tags & their use.

Please use code tags for all VBA code, including 1 liners. Future posts that breaks the rules could result in your thread being closed or deleted. :thanx:

When posting code remember to use the code tags, either [ code]your code[ /code] or [ vba]your code[ /vba] without the space after [.

If you have your Message Editor Interface set to Standard Editor, you will get extra formatting controls such as being able to select the lines in your message that contain code and click the # icon above the message to insert the code tags. With your Message Editor Interface set to Basic Editor you will not see these formatting controls. To change your interface, go to Quick Links>User Control Panel = Edit Options and scroll to the bottom to Miscellaneous Options.

Re: Vlookup function

Hi and welcome to OzGrid, Fatimah.

I think Bob meant to tell you that you were just supposed to slip in the cell reference where the revenue is.

You weren't specific about where you wanted your results nor did you show it in your example file (thank you for attaching a file).

I've modified your file, inserting a column labelled Bonus to the right of Total Revenue and inserted the following formula in cell D3 and copied the formula down to the rows below. =VLOOKUP(D3,$F$3:$G$7,2) As you can see, where Bob referred to revenue is where I've put the cell reference for revenue for that row.

Because I inserted a column, I had to slightly modify Bob's formula since the lookup values are now in columns F:G.

Please note that because all of the rows in the Total Revenue are less than 1,000,000 the bonus shown is 0.

Re: Table Lookup - 2 Input Values

Quote from EgoProwler

Thanks again guys! I'm sure I'll have more questions here very soon for some other features I need to add to this sheet.

We'll take that as a promise (and not a threat ). Looking forward to helping you.

Good job Domenic!

Re: Get Color from Color Fill on Formatting toolbar

Bob, no one has said that the suggestions are away from the original intent of the OP. And I think we all agree it's not a pretty thing to do but if that's the way he/she wants to do it, then there seems to be limited ways of doing it.

When you've been here for awhile you'll see that some things get chewed around a bit because it gets everyone thinking about alternative ways not only to accomplish what the OP wants but better or different ways.

The OP is free to pick your solution, or Barry's solution, or Roy's solution, or none of them.

Now, if the OP was paying us to give him code then the customer is definitely right. You would discuss with them why it's probably not a good idea, you might suggest alternative ways to do what they trying to do, but in the end, whoever is paying the bills has the last say.

Any of you "guys" seen any money from this thread yet?

Re: Table Lookup - 2 Input Values

Quote from EgoProwler

The input cells in this example will always be a whole number.

Then it would be a good idea to set Data Validation to Allow Whole Number Between 0 and 800 on B3 and Between 0 and 300 on B4 to prevent errors on the index formula.

Re: Production Schedule Sheet Shading

I'm also assuming that this is just a visual blocking of time and that there is nothing actually being put into the cells, and that once you decide that a cell should be filled or not, it doesn't change.

If the fill colour changes from one column to another on a weekly basis say, then you may want to do the conditional formatting.

You could do the conditional formatting say for grey, set the condition to if cell is equal to "g" and if true fill and font are grey so the text in the cell doesn't show.

But with Excel's conditional formatting, you are limited to 3 conditions on any one cell; otherwise you have to use a VBA solution.

Re: Production Schedule Sheet Shading

An example file would be a good idea but from what you say in your 2nd post, the shading isn't really dependent on whether there is anything in the cell but rather what column the cell is in.

So why not just format the appropriate cells. Select the cells, Format>Cells>Pattern and select the colour you want.

Since when you apply a fill/pattern with just the default gridlines, the border disappears. So you may want to put borders on all your cells in the work area or just around the cells with fill/pattern.

Re: Table Lookup - 2 Input Values

Quote from EgoProwler

The two yellow cells, B3 and B4, require user input:

B3 can be a number from 0 to 800
B4 can be a number from 0 to 300

You might consider using data validation on these two cells to restrict to the ranges you indicated. You can also require that the number be a whole number, for example, 10 would be valid but 10.1 would not.

Re: Production Schedule Sheet Shading

Hi and welcome to OzGrid.

Would having Excel do it for you with Conditional Formatting be what you want. You could set the formatting based on up to 3 conditions.

For example, if the cell is empty, fill with grey and place a border around the cell; otherwise fill is none with border.

Re: Get Color from Color Fill on Formatting toolbar

Quote from royUK

there's always more than one way to skin a cat (sorry Barbara) and the different solutions may prove useful to someone else, which is why I posted my suggestion originally.

First of all roy, there are some days I'd like to skin my little fur babies, so no offense (I've used the expression myself).

But I heartedly agree! Alternative solutions to solve the OPs problem and alternative ways to do something other than the way the OP is suggesting is what we're all about. Those discussion benefit not only the OP who can choose which way he/she wants to go but also others. The presentation of "alternatives" in the OzGrid world is the reason we prefer not to mark threads "SOLVED" since that tends to stifle healthy discussions.

Re: Get Color from Color Fill on Formatting toolbar

Quote from Bob Phillips

In my world, alternatives usually consitute a different way of arriving at the solution to the problem, not solving a different problem

And your point would be?????

Re: Keyboard shortcut not working

I wondered about that Norie whether the Personalized Menus and Toolbars settings needed adjustment but I couldn't duplicate the problem.

View>Toolbars>Customize...>Options will get you to the Personalized Menus and Toolbars settings J88L.

Another test to try is click on the Edit menu item. I assume that the Edit menu then expands. Then if you press "s" does the Paste Special window open?

Re: Sort-descending (errors)

Nah, just love talking about learning..I'm worn out today. :?

Re: Is the COLUMNS worksheet function volatile?

This link might be useful. Says that MS says COLUMNS() is volatile but they say when tested it's not.

They also have a test workbook to download.