Listbox how to get to appear left of cell?

[pano]

Hi all, I have the following code, a checkbox and listbox on a sheet
at the moment the list box appears to the right of the column , I
want it to appear to the left of the data entry column.

Can someone help me on what to change here, I have tried the
properties on the list box itself but it just resets, I reckon this
code controls the size,shape,location of the list box.

Thanks for the help


Option Explicit

Private Sub CheckBox1_Click()
If CheckBox1 Then
ListBox1.Visible = True
Else
ListBox1.Visible = False
End If
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim Target As Range
Dim MySel As Range

Set Target = Range("VBA_Target")
Set MySel = Intersect(ActiveCell.EntireRow, Target)
MySel.Value = ListBox1.Value

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim L As Double
Dim T As Double
Dim MaxR As Double
Dim MaxB As Double
Dim n As Integer
Dim ListR As Double
Dim ListB As Double

ListBox1.Width = 250
ListBox1.Height = 200

MaxR = Cells(1, 256).Left + Cells(1, 256).Width
MaxB = Cells(65536, 1).Top + Cells(65536, 1).Height

If ActiveCell.Column >= 255 Then
n = 0
Else
n = 2
End If

L = ActiveCell.Offset(0, n).Left
T = ActiveCell.Top

ListR = L + ListBox1.Width
ListB = T + ListBox1.Height

If ListR >= MaxR Then
L = MaxR - ListBox1.Width - (MaxR - ActiveCell.Offset(0,
-1).Left)
End If

If ListB >= MaxB Then
T = MaxB - ListBox1.Height
End If

ListBox1.Top = T
ListBox1.Left = L
End Sub

 

[pano]

My thanks to RORYA for the solution

This version will put it on the right if it will fit, the left if not:

Code:


Option Explicit
Private Sub CheckBox1_Click()
ListBox1.Visible = CheckBox1.Value
End Sub
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim Target As Range
Dim MySel As Range

Set Target = Range("VBA_Target")
Set MySel = Intersect(ActiveCell.EntireRow, Target)
MySel.Value = ListBox1.Value
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim L As Double
Dim T As Double
Dim MaxR As Double
Dim MaxB As Double
Dim n As Integer
Dim ListR As Double
Dim ListB As Double

With ListBox1
.Width = 250
.Height = 200

' offset listbox to the left
L = ActiveCell.Left - .Width
' if the listbox would go off screen, then move it to the right
If L < 0 Then L = ActiveCell.Left + ActiveCell.Width
T = ActiveCell.Top

' position listbox
.Top = T
.Left = L
End With
End Sub__________________
Regards,
Rory

Give someone a program, and you frustrate them for a day;
teach someone to program, and you frustrate them forever.