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

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