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

Garchu,

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: Chart Hyperlinks

Steeley7,

What it appears is that you made a Chart sheet which seems to be why you can't use the macro on it. If you create the chart on a regular sheet, then it will work. Take a look at the attached, I added a sheet and copied your graph to it.

Re: Print Macro

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

Application.Dialogs(xlDialogPrinterSetup).show

Re: To Link File In Words Via Vba

Just do something like:

Set Doc = CreateObject("Word.Application")
Doc.visible = True
Doc.Documents.Open Filename:= ("Fileaddress")

Hope this helps

Re: Dynamic Hyperlink Address From Cell Value

Thanks for the reply Andy. While building a sample to post, I have figured out the issue. If the value of the cell is a formula , it will not work using the value in a hyperlink.

Thanks anyway!

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.

=IF(ISERROR(FIND("""",A2,1))=TRUE,A2,LEFT(A2,FIND("""",A2,1)-1)&MID(A2,FIND("""",A2,1)+1,(FIND("""",A2,FIND("""",A2,1)+1))-(FIND("""",A2,1))-1)&RIGHT(A2,LEN(A2)-FIND("""",A2,FIND("""",A2,1)+1)))

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.

Sub TurnColOn()
Dim i As Integer


On Error Resume Next


LastRowa = Range("AO65536").End(xlUp).Row


For i = 2 To LastRowa
Labl1 = Range("AO" & i).Value
    ActiveSheet.Shapes(Labl1).Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection.ShapeRange.Item(1), Address:=Labl1
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10
    Selection.ShapeRange.Fill.Visible = msoTrue
    Selection.ShapeRange.Fill.Solid
Next i


End Sub

Re: Selecting Lines In Vb

Well, thanks for all your help. It appears that the worksheet got glitched or something. I have it working now. Thanks!

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: If And Between Ranges

Or you can use this which will give you Yes or No

=IF(AND(A1>96.8,A1<100.4),"Yes","No")

Re: copying specific text from cell

=Left(A1,10)

This is if the number is in A1

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: combo box output from sheet 1 to sheet 2

Hey John,

Just use this code:

Private Sub ComboBox1_click()
Sheet2.range("A1") = ComboBox1.value
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!