Posts by Andrew5

    Hi all,

    I have what is probably a simple question but I can’t figure it out.

    I made an Excel based program that uses multiple workbooks with two main workbooks (“Master List” and “Products”) that all the others pull information from.

    I run into a problem when a user clicks the close X in the upper right hand corner and the entire application closes.

    I found some code that will let me close all the workbooks but one (the code is put in the “Products” workbook in the BeforeClose event).

    For Each wb In Workbooks
    	If Not wb Is ThisWorkbook Then
    		wb.Close SaveChanges:=True
    	End If
    Next wb

    Is there a way to modify the code so it will close all the workbooks but the two main ones if someone clicks the close X button in the upper right hand corner?

    Thank you,


    I wrote code to update some workbooks. The code opens the workbooks and then activates the workbook to add the update.

    I was tired when I wrote the code to activate the workbook and it is written:


    The updates have already been sent out and it is not working on some computers. (If I change the code to


    it works fine.)

    Is there some option in the VBA editor that I can have people change on their computer so the code will run? Why does it work on some computers and not others?

    (Unfortunately, rewriting the code to add ".xls" and resending everything isn't an option.)

    Thank you for any insight.


    I found the code below that was written by Andy Pope that makes text in a textbox disappear.

    (It is a neat thing and there is more to it that just the code above. If you haven’t seen it check it out at this link

    My questions are:
    1. How do you change this code so that it will work for a textbox that is placed on a user form? (The code above was written for a textbox that is placed on a worksheet.)
    2. Can this also be done for a picture that is placed on a user form? (That is to have a picture gradually fade in to focus.)

    Thank you.

    Re: Stack Toolbar Buttons

    Thanks but I am trying to create the toolbar, that when created, will appear with the buttons already arranged on two rows.

    The users of the workbook that has the custom toolbar attached to it don’t know how to resize the toolbar so I am trying to set it up for them that when the toolbar appears it is already arranged on two rows instead of one long row.

    An example would be the Forms toolbar. When you are in Excel and open the Forms toolbar it appears as a vertical toolbar with two buttons per row instead of like another toolbar (like the formatting toolbar) that appears with all the buttons on a single row.

    I am using the code below to create the toolbar.

    I have been looking through the VBA help section for toolbars but have not come across what I need yet to make the buttons appear on two rows instead of one when the toolbar is created.

    Any help would be appreciated.

    Is there a way to stack toolbar buttons on two rows instead of one row?

    I have a floating toolbar that is created when a workbook is opened. The toolbar has 16 buttons (all in a row) and gets in the way because it is too long.

    I would like to display the toolbar with two rows of eight buttons instead of one long row of 16 buttons.

    Is there a way to create a floating toolbar that when created will display the first eight buttons and then “wrap text” /”carriage return” itself and then display the final eight buttons?

    Thank you,

    Re: Word Macro Based On Worksheet Result

    I can write the userform results to a cell in my workbook but I still can't figure out how to reference the workbook using Word VBA.

    How do you reference the workbook cell from Word?

    Thanks again.

    (P.S. Andy you are awesome! The progress meter stuff you did is really cool!)


    I am trying to write a macro for a word document that when the word document opens it will perform one of four actions based on what is selected in an Excel userform combobox.

    I can't figure out how to reference the Excel workbooks userform combobox.

    For when the Word document opens I tried to write the following code to reference the Excel userform's combobox but it doesn't work:

    If Workbooks("Book 1.xls").Userform1.Combobox1.Text = "Blue" Then 
    Call Turnallbackroundsblue
    end if

    How do I reference an excel userform using vba for word?

    If there is no way this can be done can someone let me know so I can try to set my project up a different way.

    Thank you in advance.


    I found the following code that I am using to swap cells:

    It works well with swapping ranges on the same worksheet.

    Is there a way to modify the code so that it will be able to swap ranges on different worksheets?


    I have a userform that has two comboboxs on it.

    When the user clicks and selects an item in combobox 1 the code will highlight an area of cells that I set before.

    When the user clicks and selects an item in the combobox2 I need a piece of code that will highlight a second area of cells while still keeping the first areas of cells highlighted.

    How I have it set up now if the combobox1.text = "Monday" then the cells B6:H20 are selected. But when the combox2 click event fires and selects another range of cells the first range selected by the combobox1 event is unselected. Is there a way to keep both areas selected?

    Thank you.

    Re: Combine Text With Carriage Returns

    I just finished testing out the formula that Krishnakumar had posted and it works exactly like I needed it to and I learned something new with how to set up if statements.

    You guys are the best!!! Thanks for a such a great helpsite!

    I have been trying to figure this out and just can’t seem to get it. I am hoping someone out there can help.

    I have 4 cells with text in them that I am trying to combine into one cell and not have any blank lines between the text.

    The cells are arranged like this:
    A1="One" B1=Cell where the text is combined

    I set the cell alignment in B1 to wrap text and use the following formula in B1:

    The problem is if there isn’t anything in one of the cells in column A it makes a blank line between the text in column B. I am trying to get the text to the top of B1 and not have any blank lines between the lines of text.

    Is there a way to change the formula so that it will do the following three things:
    1. If the cell A1 doesn’t have anything in it then don’t put the value in B1.
    2. If A1 has something in it and A2 has something in it then put the text of A1 in B1 and HAVE a carriage return after the A1 text.
    3. If A1 has something in it and A2 does not have anything in it then put the text of A1 in B1 but DO NOT have a carriage return after it.

    Thank you for your help in advance.


    I made an program that has been sent to others to use.

    I found a mistake with one of the userforms code. The form has checkboxes that when checked will print certain sheets. I missnumbered two checkboxes that I need to change. Instead of the code reading "If checkbox35=true then print..." I need to change the checkbox35 to checkbox55.

    What I want to do is send all the users a workbook that will have a command button that when clicked it will go into the program on their computer find the correct userform (userform2) and either fix that one line of code or else just replace the old userform on their computer with the corrected userform that will be contained in the excel workbook I will be emailing them.

    I did something like this when I had to change an incorrect linked reference on the program and I can open workbooks and change things in the worksheets but I don't know how to write a code that will update private code contained in a userform or how to just replace an old module or userform with a corrected one.

    Thank you in advance. Any help would be greatly appreciated.


    Re: Missing Reference Libraries


    I tried the code above and kept getting a runtime error '424' "Object Required" when I replaced the original code with the above code.

    I tried inserting it the long page of code and I tried making it a seperate procedure that I would call and either way I get the error that an object is required.

    Re: Missing Reference Libraries

    The code is listed below. I am new to VBA and had used the macro recorder to generate the code.

    The code will stop on the part that says "selection.replace what....."

    I am on a time crunch for the project and would have lots of code to rewrite. I will do it if time permits to make the program work in 2000 but is there anyway to take a newer object library and install it on an older computer?

    Thank you!

    I made a project in Excel 2002 that works great in that version of Excel and in Excel 2003 but when I try it on a computer that has Excel 2000 I get a runtime error that an object library is missing. The references that I used were Microsoft Office 10.0 Object Library, Microsoft Word 10.0 Object Library, and Microsoft Forms 2.0 Object Library. When I didn't see them listed in the references list on the older computer I tried copying the files for these 3 references to the older computer. I then opened the VB editor and clicked Tools and References and clicked the browse button. I can locate the references that I copied to the older computer but when I click on it to try to add it nothing happens.

    I also tried to select the Microsoft Office 9.0 Object Library but the code won't run using it.

    I am very new with VBA and am in a bind because I need to make the program work in Excel 2000. Is there an easy fix to getting these 3 reference libraries onto the older computer?