Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox how to get to appear left of cell?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox how to get to appear left of cell?
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can i no margin string in listbox from left | Excel Programming | |||
align left and right in listbox | Excel Programming | |||
How to make a cell appear in upper left (top left) corner of works | Excel Programming | |||
How to add picture to the left corner of listbox index | Excel Programming | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming |