Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / February 2006

Tip: Looking for answers? Try searching our database.

The reel address from a combobox  ( bob phillips)

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
alvin Kuiper - 21 Feb 2006 16:35 GMT
Hi

I use this to get some value in 2 comboboxe:
FIRST:
Private Sub combo_sedatoer_DropButtonClick()
Dim i As Long
Dim iStart As Long
Dim iEnd As Long
Dim dtePrev As Date
Dim iArray As Long
Dim ary
   dtePrev = 0: iArray = 1
   ReDim ary(1 To 2, 1 To 1)
   With Worksheets("Ark1")
       Me.combo_sedatoer.Clear
       For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
           If .Cells(i, "A").Value <> dtePrev Then
               If i <> 1 Then
                   ReDim Preserve ary(1 To 2, 1 To iArray)
                   ary(1, iArray) = dtePrev
                   ary(2, iArray) = Range("A" & iStart & ":A" & iEnd).Address
                   iArray = iArray + 1
               End If
               iStart = i
               iEnd = i
               dtePrev = .Cells(i, "A").Value
           Else
               iEnd = i
           End If
       Next i
       ReDim Preserve ary(1 To 2, 1 To iArray)
       ary(1, iArray) = dtePrev
       ary(2, iArray) = Range("A" & iStart & ":A" & iEnd).Address
       Me.combo_sedatoer.List = Application.Transpose(ary)
   End With
End Sub
Private Sub combo_sedatoer_Click()
Dim hvorb As String
Dim s1 As String

hvorb = Me.combo_sedatoer.List(Me.combo_sedatoer.ListIndex, 1)
s1 = Replace(hvorb, "A", "B")

Dim rng3 As Range
Sheets("Ark1").Select
 Set rng3 = Range(Range("Ark1!" & s1), Range("ark1!" & s1))
Me.combo_timer.List = rng3.Value
End Sub

What i need i to see the cell value there are in a cell right to the address
from
value i choose in Me.combo_timer.

Like all value in Me.combo_timer.list comes from collumn B and when i selct
a calue in Me.combo_timer then i want to get the value from collumn C from
the same row as selcted in Me.combo_timer
I have try with lisindex but dosn't work

regards
alvin
Toppers - 21 Feb 2006 16:56 GMT
Hi
    Somthing like this should work ....

Private Sub combo_Timer_Change()
' Assume first item in this combobox(.list) is from  row 2 in column B
'the ....
row=combo_timer.listindex+2
set ws1.worksheets("Sheet1")  ' <== change as required
myValue=ws1.cells(row,"c")  ' Value from column C
End Sub

HTH

> Hi
>
[quoted text clipped - 56 lines]
> regards
> alvin
alvin Kuiper - 21 Feb 2006 17:57 GMT
Hi toppers
Thanks
But i get a eroor in
set ws1.worksheets("Sheet1")  
it say i shall have = after set ws1.worksheets("Sheet1")  
regards
alvin

> Hi
>      Somthing like this should work ....
[quoted text clipped - 69 lines]
> > regards
> > alvin
alvin Kuiper - 21 Feb 2006 18:07 GMT
Hi
I have try
this
Row = combo_timer.ListIndex + 2
Sheets("Ark1").Select
myValue = Cells(Row, "c") ' Value from column C
txtvaerdi.Value = myValue

But again it is not the right value
but the value from the index

regards
alvin

> Hi
>      Somthing like this should work ....
[quoted text clipped - 69 lines]
> > regards
> > alvin
alvin Kuiper - 21 Feb 2006 18:59 GMT
Now i use this in my first combobex
Dim rng3 As Range
Sheets("Ark1").Select
 Set rng3 = Range(Range("Ark1!" & s1), Range("ark1!" & s1))
 For Each c In rng3
       combo_timer.AddItem c.Value
    its working

s1 = $b$1:$b$22 or $b1:$b21 -- can bee many value just you can see

so the vlaue in  combo_timer are from the range in S1

Now i just have to know where the real address are when i choose
a value.

regards
alvin

> Hi
>      Somthing like this should work ....
[quoted text clipped - 69 lines]
> > regards
> > alvin
eseitomer@gmail.com - 21 Feb 2006 17:07 GMT
Try adding 1 to Me.combo_sedatoer.ListIndex and use that value as your
row.

CurrentRow = Me.combo_sedatoer.ListIndex + 1

Remember ListIndex only specifies where in the list it is, not the row
number. Depending on how many rows you have before where you start
counting, you might have to add more than one.

-E
alvin Kuiper - 21 Feb 2006 18:22 GMT
No i dosn't work
because the listindex is not the real Address
but the Adress from the index

I have i Collumn A the same date many times
Maybe from A1 to A21 I have the same Date
and again From
A22 to A30 i have the same date
But in my combebox i only see to dates
Then i get the range where the dates are and use this
whit replace A to B in my second combobox
combo_timer

Here i get all the value from B But it can bee from B22 to B31
or B1 to B21 and so on

So listindex give me maybe $B$1
But teh real address can bee $Bb$22
Because it the first value in  combo_timer
If i have choose the second calue of dates in my firstcombobox

Regards

Alvin

> Try adding 1 to Me.combo_sedatoer.ListIndex and use that value as your
> row.
[quoted text clipped - 6 lines]
>
> -E
eseitomer@gmail.com - 21 Feb 2006 18:34 GMT
Hmm.. Have you tried storing the real address in the array?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.