Posts by Eggcel

    I have 3 Activex listboxes on 1 worksheet. When I make them visible, I cannot scroll to the last item in the list. I have searched for a solution, but they all say to change the IntegralHeight, which I have done.



    Is there something I am missing? Thank you for your time.

    Re: Keep Activex combobox dropdown open after changing the Listfillrange


    What I wanted to do, which Debra's original code does not cover, was to use the same combobox to select from two different named ranges. The problem that I was experiencing, as stated in my original post, is that when I change the listfillrange to the second named range the dropdown will not remain open. I have been trying to change my code using listboxes, per your recommendation. I am running into quirky issues with the listboxes, such as the change event firing twice.


    I have added 4 listboxes to the worksheet. 1 which lists the available shifts, and 3 that list the employees on each shift. When the shift listbox loses focus I am clearing the .value. When I make the listbox visible again for the next selection, the first value is highlighted and shows as the value for the listbox. Is there a way to prevent this from happening. The change event only fires if I select one of the other two options. If I selected the top, highlighted option it does not fire the change event since it is already set as the value. I have done much work with macros, but this is my first experience with listboxes. I have been doing lots of research, but have not found a solution yet.

    Re: Keep Activex combobox dropdown open after changing the Listfillrange


    I am trying it with listbox, but when I try to change the Listfillrange with the first selection it fires the change event. The listfillrange is unchanged.



    Also, by directing me toward the Listbox, are you saying that what I am trying to do with the combobox is not possible?

    Hopefully my title is sufficient enough.


    My issue is that I am trying to use an activex combobox to populate cells on a sheet which is basically a list of employees and the various stations that they will work at for the shift. I am using data validation in the cells which gives the combobox it's intial Listfillrange. When the user double clicks in the cell, this code runs:


    The combobox appears over the activecell with the dropdown open and the user selects the shift which triggers the change event.



    This is where my problem occurs. The combobox dropdown does not stay open for the user to select the employee. It still works, since they can click the dropdown arrow and it will display the list of employees, which they can select and have it populate the cell as intended. In my code, I tell the combobox drop down to open, and it does until the end of the macro and then it closes. I have tried several variations of this, and have been unable to keep the dropdown open. It creates one extra step that should not be needed. Thank you for reading, and I appreciate any insight anyone can give me on this. I have been racking my brain and burning up google looking for answers.

    Re: How to fetch the table from ASP web site and save in Excel


    I am not sure what further advice I can give you without being able to access the web page. The code that I posted is pretty standard for grabbing tables. You could loop through all of the TD tags and write those to cells and then pick out what you need possibly.

    Re: Bypass or Accept Internet Explorer Dialogue Box When Exporting Webform Results


    What OS are you using? If it is not windows 7 you can go to my computer>tools>folder options>file types. Scroll down to the excel file extensions(they are at the bottom and begin with X). Select these and then click advanced. In the new window uncheck the confirm open after download box. The open,save,cancel box should no longer display. I was playing with it this morning and remembered that I had done this with a workbook I created some time ago. When I rechecked this box I started to get the dialog box when I ran the macro. If you have windows 7 there is no direct way to alter the file types settings(from what I have read) but there are programs that give you that functionality.

    Re: Excel 07 VBA to download from FTP



    This will create the IE object and open the website, but if it's pasword protected I would assume you need to include submitting the username and password in the code as well. I am happy to try to help you, but depending on your knowledge of VBA it may be difficult to get you where you need to go.

    Re: Excel 07 VBA to download from FTP


    It is probably a matter of opening the site and selecting the appropriate link to the file. Without the source code it would be difficult to provide you code to access the appropriate file links.

    Re: Selecting rows between 2 cell values


    This should do it, or get you close


    Code
    Sub this()
    Dim rng As Range
    Dim Rpttotals As Long
    With Sheets(1)
    Rpttotals = Application.WorksheetFunction.Match("Report Totals:", .Range("A:A"), 0) - 1
    Set rng = .Range("A9:F" & Rpttotals)
    End With
    rng.Copy
    Sheets(2).Cells(1, 1).PasteSpecial Paste:=xlPasteAll
    End Sub

    Re: Enter day of month in one cell (ex. 15), second cell calculates day of week (ex.


    We all start new at somepoint.


    Since you are using the sheetname as the month and year reference, use this formula
    =IF($B2<>"",DATE(YEAR(TEXT(MID(CELL("filename"),FIND("]",CELL("filename"))+1,255),"mmm-dd")),MONTH(TEXT(MID(CELL("filename"),FIND("]",CELL("filename"))+1,255),"mmm-dd")),DAY($B2)),"")


    This assumes that the date value you will enter is in B2. You can change this cell to whatever location you are actually using.


    change the cell format to "ddd" to display the three letter day of the week(under custom format and type ddd in the display box)