Posts by Brister

    Re: Form Control ListBox Font turning White

    Never mind, I figured out a way to make the reference cell dynamic in the named range. For other people who may come across this issue, here is the Named Range formula (Note that I have a header that will not get deleted, but obviously did not want in the list box): =OFFSET(OFFSET(Build!$AI$1,1,0),0,0,501-COUNTBLANK(Build!$AH$2:$AH$500)-2). This way if I delete the top item it doesn't change my range. Thanks!

    So, because of the bugs with ActiveX controls, i.e. the resolution change causing zoom issues and font resizes, I am switching to a Form Control List Box. I have it working fine, but whenever I add an item to the ListBox, which requires me to change the listrange via VB, it is turning the font white. I have to go back reselect items to refresh the Listbox for it to go back to black font. It is very odd and I am not touching anything with font within so obviously another bug. I can select the items in the List box and they are correct, you just can't see the what the text is to select the correct item in the list.

    Now I tried to switch to a dynamic Named range, but my issue is, if I remove the top item in the listbox, it corrupts the named range because it gives a #ref error because the first line was removed. Anyone have any suggestions? Thanks!

    Re: Automatically Going To Next Tab Index

    You can also adjust your maxlength of your TextBox properties to only allow a certain amount of characters.
    You could code if you want, here is an example if you wanted to limit Textbox1 to only 4 characters:

    Private Sub TextBox1_Change()
    If Len(Sheet1.TextBox1.Value) = 4 Then Sheet1.TextBox2.Activate
    End Sub

    Re: Sumif - Referece To Cell On Another Worksheet


    I am not fully understanding what you are trying to do. Are you checking the sum of B3:B75, or looking at each cell in the range. If you are looking at the sum, than you can use:

    =If(Sum(G3:G75)>=City Wide!B6,Sum(I3:I75),"")

    Re: Print Macro

    If you are looking for just the dialog box line, you can do:


    Re: Hide Blank Rows From Bottom Up

    Add a +1 to the end of this line like so. You have it looking for the last row, but the last row is data, so you need to add 1 for the first blank row.
    Hope this helps.

    Blnk = Range("A65536").End(xlUp).Row +1

    Re: Find And Delete Quotation Marks At The End Of A String

    I am not sure if I understand what you need, I assume you want to remove quotation: Here is a formula that will work to remove the quotation marks, and if there aren't any, will use the cell value as is. Hope this is what you needed.


    Hey guys,

    I am hitting a brick wall on this one. I am trying to assign hyperlinks to objects based on the cell value. Here is my code that colors the objects that have the same names as in Column AO. I am trying to also add a hyperlink to those same objects with the cell value in the list. The problem is while a hyperlink is added, I can not get the value of the cell to show up in the hyperlink address. Any help would be much appreciated.

    Re: Selecting Lines In Vb

    That code doesn't even work for me. It will move the first line to what the second line is supposed to go, which shows that it keeps it selected instead of switching selection to Line 10. I even tried putting a cell select line in between, but then it just ignored the Line 10 commands. I am purplexed.

    I am getting an error: Run-Time error '-2147024809 (80070057)': The Item with the specified name wasn't found.

    I tried a on error resume next, but that just skipped the second line code. I even rebuilt from scratch to make sure everything was named properly to no avail.

    Hey guys,

    I have two lines (in a sheet) that I am trying to move based on a formula. The wierd thing is I can get the first one to work fine, but when I select the next line it will not work. I have Line 2, and Line 10. Basically the code is:

    ActiveSheet.Shapes("Line 2").select
    Selection.ShapeRange.Top = 159
    ActiveSheet.Shapes("Line 10").select
    Selection.ShapeRange.Top = 300

    For some reason it just moves Line 2 no matter what I do.

    Any help is much appreciated.

    Re: Counts rows in a range

    Hey TJolly, are you looking for something like:

    Private Sub CommandButton1_Click()
    Dim LastRow As Long
    LastRow = Sheet1.Range("A65336").End(xlUp).Row + 1
    Sheet1.Range("A" & LastRow).Value = "Another Row"
    End Sub

    Which finds the last Row and puts whatever you want in it.

    Re: combo box output from sheet 1 to sheet 2

    If you need to add the numbers to the comboBox, there are a few ways to do it. If it will always be 1 to 24 then you can use this code:

    Private Sub Worksheet_Activate()
    Dim i  As Integer
    For i = 1 to 24
       ComboBox1.AddItem i
    Next i
    End Sub

    Re: Vlook up or another way

    Thanks for the response. Andy, that is the idea I am looking for, but I am trying to make heads or tails out of the code which makes somewhat sense (Ive never seen the StrComp before so I learned something new) but what doesn't make sense is the textBox1 value.

    The line:

    If ComboBox2.ListIndex >= 0 Then
            TextBox1.Text = ComboBox2.List(ComboBox2.ListIndex, 1)
        End If

    I am looking at that, and I don't understand how based on the combobox2 listindex, it is able to pull the next column data. How can I pull the last column (D) for that data? Thanks for your help!