Posts by Steve at work

Re: Clear blank entries from a combo box

Alright, I worked it out myself, though it was quite complicated.

Re: Add Unique Items to ComboBox Control

Alright, thank you.

Re: Clear blank entries from a combo box

Is what I am trying to do not possible perhaps?

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

Hi,

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
   cboTemp.RemoveItem
   End If
Next

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,

Steve

Hi,

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.

Hi,

I have info in a spreadsheet (located in column 11) that I want to appear at the top of every page after column 11 when I print (though not at the top of the 1st page). I can't figure out how to do this. Any help would be appreciated.

Re: Macro to hide row

Oops, sorry. Glad it works though.

Re: Macro to hide row

This should do what you want it to.

Dim MyRange As Range 
Dim MyColumn As Integer 
 
Set Myrange = Range("D5") 
 
Myrange.select 
 
Do 
     
    MyColumn = MyRange.Column
    If myrange.value = "False" Then 
        Columns(MyColumn).Select 
        Selection.EntireColumn.Hidden = True 
    End If 
     
    Set Myrange = Myrange.offset(0,1) 
Loop Until MyColumn = 27

Re: Insert Vertical Text in Cells based on Textbox Input

You are right, it does. I don't know what I was talking about.

Thanks a lot.

Re: Event if Option Button is selected, not just clicked on

Thank you. It works perfectly. (I used workbook load).

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: Insert Vertical Text in Cells based on Textbox Input

Ok, thanks. I will give it a try.

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

If you want it to also run when someone uses the manual unhide option, let me know and I'll think if I can figure out a way to do it.

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

ie

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

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