Posts by Steve at work

    Re: Clear blank entries from a combo box

    My combobox is actually already drawing from a Data Validation field, in which I've specified it to ignore blanks. This doesn't seem to help (perhaps because the blanks actually contain formulae?). I don't see how I could further combine this with an advanced filter, though if you do know and could let me know, that would be much appreciated.

    Re: Clear blank entries from a combo box

    The range is already sorted to a specific order (ie, the order in which, optimally, they should be selected); however, as you select them, every time that you activate the combo box, you are getting more and more blank space


    I have a VBA combobox in a worksheet. This combobox is linked to a range. As items are selected from the combobox, the selected items disappear from the range (as the range is actually drawing from a database, and I am using the COUNT function).

    My problem is that when the item is selected in the combobox, when the combobox is accessed again, there's a blank. These blanks keep accumulating, making it visually unappealing. Is there any way to remove all blank entries from a combo box, or even get a combo box to sort its list alphabetically?

    I've been trying to use

    For x = 1 To cboTemp.ListCount
        If cboTemp.?????? = "" Then
       End If

    But I don't know what property of the combobox I should be referring to where I have the question marks. Either a modification of the partial solution that I have up, or a completely new solution, would be much appreciated.

    Thank you in advance,



    I have a database of data that looks like this
    Centre Name Training Type Expert 1 Expert 2 Expert 3 Expert 4
    MyCentre MyTraining Me
    MyCentre MyTraining Me
    MyCentre MyTraining Me
    MyCentre OtherTraining Me
    NewCentre NewTraining NewPerson

    I am trying to create a user form (in VBA) with a combo box that has each centre name appear only once (despite the fact that in the database each occurs multiple times). When a specific centre is selected, this brings up all the different training types associated with that centre in a separate list box. When that training type is selected, it should bring up all of the experts in that training type (for the specific centre).

    I should mention that the range is static (though the data is always contiguous) as the database is updated on a going forward basis.

    Thanks for any help you can offer.

    Re: Macro to hide row

    This should do what you want it to.

    I have 3 option buttons. I have events connected to each one. The thing is if someone has been using the worksheet and already has an option selected when they load, then they won't click the option box (as it is already selected), and the necessary events won't run. I either want to find a way to select a specific option button upon activating a worksheet, or I'd like to ahve the events run in the case that the option box is selected, not just clicked on. Can anyone help me with this?

    I am trying to create a password protected workbook. This workbook has 2 sheets. The first sheet has 3 check boxes. If the first check box is clicked, I want a series of cells in column 5 to unlock on both sheets, and all other cells to remain locked. If check box 2 is ticked, I want column 6 unlocked, and all other cells locked. If check box 3 is ticked, I want column 7 unlocked, and all other cells locked.

    Thank you very much in advance for any help you can offer me with this problem.

    Re: Insert Vertical Text in Cells based on Textbox Input

    It is very close (and will be good enough if what I want isn't possible), but it gives the wrong orientation. I want the text to read from top to bottom, not bottom to top. (I tried just to use orientation change of 90, but it gave the same result as a change of -90 for some curious reason)

    Re: Editing Run-time error messages

    Do you want this for a specific error type, or is this any error at all?

    I ran into this problem when I wanted to deal with input that was only supposed to be dates, but people were putting in text like "vacation" or "sick". I wanted to be able to identify these errors, and create a table of their cell locations so that the user could go and correct them.

    If that is the kind of thing you want to do, let me know and I could probably help you with it. Just say what specific error you are looking for.

    I'm stuck on a bit of a problem. I am designing a from with a variable number of titles. The user should be able to insert a title into a column by typing in the title that he wants into a text box, and pressing a command button that I have created (named add).

    The problem arises when I try to get the text to lie vertically as opposed to horizontally (in the cell, not as a text box. If this can only be done as a textbox, let me know). Is there any way to do this?

    Re: Macro to hide row

    Ok, here's what you do. Add a module to your project, declare option explicit and declare a variable called CountRun. ie

    Option Explicit
    Global CountRun as Integer

    Then in the code to get the macro to run add an If ... Then loop around all of the steps and at the very end, add a line that says countrun = 1


    If countrun < 1 then
    (rest of steps)
    countrun = 1

    Then add a line countrun = 0 in your unhide macro.