Solved

listbox transfer to a textbox

Posted on 2007-11-18
6
433 Views
Last Modified: 2013-12-26
Experts,
1) This code below isnt poulating my listbox 'fault_listbox'. I dont know why?! If fault range is in another worksheet to the current will this effect it?
With fault_listbox
    .MultiSelect = 1
    .RowSource = [FAULT_RANGE].Address
End With
2) I want the user to be able to pick mulitple values from the listbox and then hit a command button to display those selected in a textbox. They can be dragged or displayed via a commandbutton but nothing else. The user must not be able to select the same item within the listbox twice.
Could anyone help me to do this please?

Thanks

p.s i'm after some additional help for a fee to develop code -(too much foe EE)- please let me know or refer me to people who could be interested
0
Comment
Question by:simondopickup
  • 3
  • 2
6 Comments
 
LVL 12

Expert Comment

by:chandru_sol
ID: 20307676
Can you post your code?
0
 

Author Comment

by:simondopickup
ID: 20307709
Sure,

I want to select items from the listbox - display them in the text box and then execute the simulation. Currently the simulation finds and ignores any values from listbox that i have selected (but i cant select them because valules are not loading into the box. But when i have sorted the listbox problem out - i want to changesuch that the simulation ignores values from the textbox instead...








Private Sub UserForm_Initialize()
 

'set the user defined frame live or disable

Set wksdata = Worksheets("DATA STORE")

Set wks_week = Worksheets("OEE Weekly Summary")

Set wks_OEE = Worksheets("OEE History")
 
 

wksdata.Cells(6, 1).ClearContents

wksdata.Cells(6, 2).ClearContents
 

If wksdata.Cells(5, 3) = "RECORDING!" Then

OEE_History_frame.Enabled = True

Day_Record_Frame.Enabled = True

day_return_picker.Enabled = True

Else

OEE_History_frame.Enabled = False

Day_Record_Frame.Enabled = False

End If
 

Fault_Weekly_Toggle = False

OEE_Weekly_Toggle = False

OEE_Period_Toggle = False

Fault_Period_Toggle = False

OEE_date_start_pick.Enabled = False

OEE_date_end_pick.Enabled = False
 

'day_return_picker.Format = dtpCustom

day_return_picker.CustomFormat = Chr(32)

Analysis_Userform.Label15.Visible = False

Analysis_Userform.Label8.Caption = ""

Analysis_Userform.Label14.Caption = ""

Analysis_Userform.Label10.Caption = ""

Analysis_Userform.Label12.Caption = ""
 

Analysis_Userform.OEE_Fault_Page.Value = 0
 

With fault_listbox

    .MultiSelect = 1

    .RowSource = [FAULT_RANGE].Address<===not loading values

End With
 

Private Sub fault_listbox_click()

'no code entered yet

End Sub

Private Sub fault_listbox_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
 

End Sub

' this takes a displays the faults in another textbox

Function GetSelectedItems(lstItems As MSForms.ListBox, Optional strDelimiter As String = ",") As String

   Dim lngIndex As Long, strData As String

   With lstItems

      For lngIndex = 1 To .ListCount

         If .Selected(lngIndex - 1) Then

            strData = strData & strDelimiter & lstItems.List(lngIndex - 1)

         End If

      Next lngIndex

   End With

   GetSelectedItems = Mid$(strData, Len(strDelimiter) + 1)

End Function

Private Sub fault_select_button_Click()

   Me.show_faults_textbox = GetSelectedItems(Me.fault_listbox, vbLf)

End Sub
 

Private Sub run_sim_button_Click()<==currently looks at listbox selected values. Need to change to values that have been added to the textbox...

Dim i As Long, cel As Range, ckValue As String

Dim oDic As Object
 

Set oDic = CreateObject("Scripting.Dictionary")
 

For i = 0 To fault_listbox.ListCount - 1

    If fault_listbox.Selected(i) = True Then

        ckValue = fault_listbox.List(i)

        If Not oDic.Exists(ckValue) Then

            oDic.Add ckValue, i

        End If

    End If

Next i
 

For Each cel In [FAULT_RANGE]

    ckValue = cel.Value

    If Not oDic.Exists(ckValue) Then

        ' Cell value not found in Ignore List.

        MsgBox "Process this cell/row.  Cell value = " & cel

    End If

Next cel
 

Set oDic = Nothing
 

End Sub

Open in new window

0
 

Author Comment

by:simondopickup
ID: 20307920
Can anyone help with this one!?!
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 20308282
.RowSource = "FAULT_RANGE"

Why not just set it at design time, incidentally?
0
 

Author Comment

by:simondopickup
ID: 20308580
sorry, rorya i dont think i follow. That is only returning the first cell of the range into the textbox. At the moment. I am trying to incorporate one of *your own* solutions in the past to allow the user to drag or click on an a commandbutton to select values from the listbox (once it loads correctly) and stick them in a textbox for the user to see clearly.

Here is the code currently but i still cant load that listbox - it should have 3 values and is showing one. Should i be putting something in the click event ?


Private Sub UserForm_Initialize()

With fault_listbox

    .MultiSelect = 1

    .RowSource = "FAULT_RANGE"

End With
 

End Sub

Private Sub fault_listbox_click()

End Sub

Private Sub fault_listbox_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

End Sub

' this takes a displays the faults in another textbox

Function GetSelectedItems(lstItems As MSForms.ListBox, Optional strDelimiter As String = ",") As String

   Dim lngIndex As Long, strData As String

   With lstItems

      For lngIndex = 1 To .ListCount

         If .Selected(lngIndex - 1) Then

            strData = strData & strDelimiter & lstItems.List(lngIndex - 1)

         End If

      Next lngIndex

   End With

   GetSelectedItems = Mid$(strData, Len(strDelimiter) + 1)

End Function

Private Sub fault_select_button_Click()

   Me.show_faults_textbox = GetSelectedItems(Me.fault_listbox, vbLf)

End Sub
 

Private Sub run_sim_button_Click()

Dim i As Long, j As Long, k As Long, cel As Range, ckValue As String

Dim oDic As Object
 

Worksheets("OEE History").Visible = True

Worksheets("DATA STORE").Visible = True
 

Set oDic = CreateObject("Scripting.Dictionary")

Set wksdata = Worksheets("DATA STORE")

Set OEEwkset = Worksheets("OEE History")
 

For i = 0 To fault_listbox.ListCount - 1

    If fault_listbox.Selected(i) = True Then

        ckValue = fault_listbox.List(i)

        If Not oDic.Exists(ckValue) Then

            oDic.Add ckValue, i

        End If

    End If

Next i
 

'clear any stored selected data

wksdata.Select

With wksdata

    .Rows("9:11").ClearContents

End With

    

OEEwkset.Select

'Find the row that MTTF and MTTR data exists

Set MTTF_rw = OEEwkset.Columns(1).Find _

        ("MTTF (hrs)", OEEwkset.Cells(OEEwkset.Rows.Count, 1), xlValues, xlWhole)

        

'write new data to the data store

j = 1

k = 2

For Each cel In [FAULT_RANGE]

    ckValue = cel.Value

    If Not oDic.Exists(ckValue) Then

        wksdata.Cells(9, j) = cel.Value

        wksdata.Cells(10, j) = OEEwkset.Cells(MTTF_rw.Row, k).Value

        wksdata.Cells(11, j) = OEEwkset.Cells(MTTF_rw.Row + 1, k).Value

        j = j + 1

        ' Cell value not found in Ignore List.

        MsgBox "Process this cell/row.  Cell value = " & cel

    

    End If

    k = k + 1

Next cel
 

Set oDic = Nothing
 

Worksheets("OEE History").Visible = False

Worksheets("DATA STORE").Visible = False
 

run_sim
 

End Sub

Open in new window

0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 20319191
Is FAULT_RANGE a row or column? If it's a row, you could try:

With fault_listbox

    .MultiSelect = 1

    .List = Application.Transpose([FAULT_RANGE].Value)

End With

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now