Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveCell.Top question
I have started working on a sheet that was put on the back burner. I have
since upgraded to Office 2003 (with XP). I do not know if this is a factor or not. The macro I wrote has a listbox that comes up if a cell is clicked on. Previously I had it so that if the cell was in the top half of the window the top of listbox was level with the active cell. If the active cell was in the bottom half then the bottom of the listbox was level with the active cell. What seems to happen now is that the ActiveCell.Top give a value based on where the cell is from the very top of the sheet instead of the top of the open window. I may not be remembering this correctly but I do know that I had it working properly prior to upgrading to 2003 (from 2002 I think). Is there a change in the way ActiveCell.Top is reflected or am I just screwed up? The code is: If Target.Column = 7 Or 8 Or 9 Or 10 Or 11 Or 12 Or 13 Then If Target.Row 10 Then If Cells(ActiveCell.Row, 3) < "" Then AWh = ActiveWindow.Height ACt = ActiveCell.Top ACTdiff = (ACt + 215 + 50) If AWh < ACTdiff Then BugBoxTop = ACt - 205 Else BugBoxTop = ACt End If For i = 7 To 8 If Target.Column = i Then With sh.Shapes.AddFormControl(xlListBox, ActiveCell.Left _ + ActiveCell.Width + 10, BugBoxTop, 50, 215) .Name = "Listbox1" .ControlFormat.AddItem "1" .ControlFormat.AddItem "2" .ControlFormat.AddItem "3" .ControlFormat.AddItem "4" .ControlFormat.AddItem "5" .ControlFormat.AddItem "10" .ControlFormat.AddItem "15" .ControlFormat.AddItem "20" .ControlFormat.AddItem "25" .ControlFormat.AddItem "30" .ControlFormat.AddItem "50" .ControlFormat.AddItem "60" .ControlFormat.AddItem "70" .ControlFormat.AddItem "75" .ControlFormat.AddItem "100" .ControlFormat.AddItem "150" .ControlFormat.AddItem "200" .ControlFormat.AddItem "250" .ControlFormat.AddItem "300" .ControlFormat.AddItem "500" .ControlFormat.AddItem "TNTC" .ControlFormat.AddItem "(blank)" End With sh.ListBoxes("Listbox1").OnAction = "Box_Click2 " End If Next i |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveCell.Top question
I am not sure i understand your objective but i can tell you that the first
if statement will not work. If Target.Column = 7 Or 8 Or 9 Or 10 Or 11 Or 12 Or 13 Then You must say if target.column = 7 or Target.column <=13 Try this cleanup first and then post me if you need more help Stewart Rogers PS: I would use a command button instead of a floating list box "Brad K." wrote: I have started working on a sheet that was put on the back burner. I have since upgraded to Office 2003 (with XP). I do not know if this is a factor or not. The macro I wrote has a listbox that comes up if a cell is clicked on. Previously I had it so that if the cell was in the top half of the window the top of listbox was level with the active cell. If the active cell was in the bottom half then the bottom of the listbox was level with the active cell. What seems to happen now is that the ActiveCell.Top give a value based on where the cell is from the very top of the sheet instead of the top of the open window. I may not be remembering this correctly but I do know that I had it working properly prior to upgrading to 2003 (from 2002 I think). Is there a change in the way ActiveCell.Top is reflected or am I just screwed up? The code is: If Target.Column = 7 Or 8 Or 9 Or 10 Or 11 Or 12 Or 13 Then If Target.Row 10 Then If Cells(ActiveCell.Row, 3) < "" Then AWh = ActiveWindow.Height ACt = ActiveCell.Top ACTdiff = (ACt + 215 + 50) If AWh < ACTdiff Then BugBoxTop = ACt - 205 Else BugBoxTop = ACt End If For i = 7 To 8 If Target.Column = i Then With sh.Shapes.AddFormControl(xlListBox, ActiveCell.Left _ + ActiveCell.Width + 10, BugBoxTop, 50, 215) .Name = "Listbox1" .ControlFormat.AddItem "1" .ControlFormat.AddItem "2" .ControlFormat.AddItem "3" .ControlFormat.AddItem "4" .ControlFormat.AddItem "5" .ControlFormat.AddItem "10" .ControlFormat.AddItem "15" .ControlFormat.AddItem "20" .ControlFormat.AddItem "25" .ControlFormat.AddItem "30" .ControlFormat.AddItem "50" .ControlFormat.AddItem "60" .ControlFormat.AddItem "70" .ControlFormat.AddItem "75" .ControlFormat.AddItem "100" .ControlFormat.AddItem "150" .ControlFormat.AddItem "200" .ControlFormat.AddItem "250" .ControlFormat.AddItem "300" .ControlFormat.AddItem "500" .ControlFormat.AddItem "TNTC" .ControlFormat.AddItem "(blank)" End With sh.ListBoxes("Listbox1").OnAction = "Box_Click2 " End If Next i |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ActiveCell.Offset Question | New Users to Excel | |||
If activecell.column = variable then activecell,offset (0,1) | Excel Discussion (Misc queries) | |||
use of activecell | Excel Programming | |||
Activecell value | Excel Programming | |||
how to set activecell? | Excel Programming |