Value depending upon selection of Listbox data

Thanks for looking at my post

I have below VB code. Issue is I am doing something wrong in the If statement. When I remove if statement and run one condition at a time it works but when I put if statement with multiple conditions it does not work.

Any idea of where I am going wrong in the code please?

Thanks,
Jim

HTML Code: 

You've nested the Ifs within each other so the second one is tested only if the first one is true (and if that's true, the second one can't be). You can either use:

Private Sub Machine_Change()
  'Variable Declaration
         
    Dim iRow As Integer
        iRow = 1
        'Clear Combobox2 before loading items
    PGNumber.Text = ""
                If Me.Demand.Value = "This month" Then
    Do
        iRow = iRow + 1
        
    Loop Until Machine.Text = Sheets("ADDNEW").Cells(iRow, 6)
    PGNumber.Text = Sheets("ADDNEW").Cells(iRow, 7)
    
    ElseIf Me.Demand.Value = "next month" Then
    Do
        iRow = iRow + 1
        
    Loop Until Machine.Text = Sheets("ADDNEW").Cells(iRow, 6)
    PGNumber.Text = Sheets("ADDNEW").Cells(iRow, 8)
    
    ElseIf Me.Demand.Value = "next 3 month" Then
    Do
        iRow = iRow + 1
        
    Loop Until Machine.Text = Sheets("ADDNEW").Cells(iRow, 6)
    PGNumber.Text = Sheets("ADDNEW").Cells(iRow, 9)
    
End If

End Sub

or use a Select Case construction:

Private Sub Machine_Change()
  'Variable Declaration
         
    Dim iRow As Integer
        iRow = 1
        'Clear Combobox2 before loading items
    PGNumber.Text = ""
    Select Case Me.Demand.Value
       Case "This month"
          Do
              iRow = iRow + 1
          
          Loop Until Machine.Text = Sheets("ADDNEW").Cells(iRow, 6)
          PGNumber.Text = Sheets("ADDNEW").Cells(iRow, 7)
    
       Case "next month"
          Do
              iRow = iRow + 1
        
          Loop Until Machine.Text = Sheets("ADDNEW").Cells(iRow, 6)
          PGNumber.Text = Sheets("ADDNEW").Cells(iRow, 8)
    
       Case "next 3 month"
          Do
            iRow = iRow + 1
        
          Loop Until Machine.Text = Sheets("ADDNEW").Cells(iRow, 6)
          PGNumber.Text = Sheets("ADDNEW").Cells(iRow, 9)
    
End Select

End Sub

Since the loop code is the same other than the column number, you could simplify to:

Private Sub Machine_Change()
  'Variable Declaration
         
    Dim iRow As Long
    Dim colNum as Long
     iRow = 1
     'Clear Combobox2 before loading items
     PGNumber.Text = ""
    Select Case Me.Demand.Value
       Case "This month"
          colNum = 7
    
       Case "next month"
          colNum = 8
       Case "next 3 month"
         colNum = 9
End Select
If colNum <> 0 then
   Do
      iRow = iRow + 1
          
   Loop Until Machine.Text = Sheets("ADDNEW").Cells(iRow, 6)
   PGNumber.Text = Sheets("ADDNEW").Cells(iRow, colNum)
end if
End Sub

Thanks a lot for your reply. That's work perfect

Thanks again for your help

I have one more condition which I need to insert in this loop condition. Sorry to include in my previous post as I just found it.

I have another textbox named as Textbox1 and its caption is "Available Time". Lets assume I work 8 hrs a day (Monday to Friday). So 40 hrs a week excluding weekends.

Depending on data like "This Month", "next Month", "Next 3 months", "Next 6 months", "Next 12 month" and "next 24 months" I would like to automatically get number of hrs in the "TextBox1" please.

That is today is 20/05/2021. So if some one click "This month" I would like to get remaining weekdays of this month multiply by 8. Same for next months (just hold next months data) , next 3 months will hold next 3 months weekdays multiple by 8 and so on.

Is this complicated?

Thanks again

Jim

That's a totally separate question and should be asked as such, please.

Hi,

Sorry for reopening this thread again. I have modified userform and instead of combobox I have replaced with List box. That is

HTML Code: 

where Machine is now List box. Unfortunately the code is not working if I replace with List box. Any idea please?

Thanks,
Jim

Define "not working" please. What happens?

Sorry for poor explanation. What I mean is

1. If machine is combo box then PGNumber textbox is pulling information from column 7 onwards.
2. If I replace machine (combo box) with listbox then PGNumber textbox is not showing anything when I click anything from listbox.

I have attached model sheet. Instead of partnumber (which is in combo box) I am trying to get those information in list box which I have highlighted in green.

NOTE: I have not inserted any code in listbox in this model but when I tried in my actual model I am not seeing any value in PGNumber textbox which is "Total Quantity" in the userform.

I can explain more if you need more information


Thanks,
Jim

I can't debug the code if it's not there...

Sorry for not providing code

I have attached the sheet with Listbox. I am doing something wrong can you please point me that?

1. Irrespective of selection in the List box, I am getting same value in "Total time", "Longest Mins", "Shortest Mins" and "longest minus shortest" textbox
2. Secondly, When I select Demand combo box I am getting wrong value in the Total Quantity text box.
3. Thirdly, reason for changing to Listbox from combo box is I wanted to select multiple items. Is there any possibility if I select multiple items I should get those multiple items value in "Total time", "Longest Mins", "Shortest Mins" and "longest minus shortest" textbox and in "Total Quantity" text box please?

Sorry for list of questions.

Again thanks a lot for your kind support

Thanks,
Jim

Any idea about this please?

Thanks

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Update the title in the opening post of the thread, please.

Just click on the edit post button and take it from there.

I wanted to delete this post. sorry

Directly below your opening post are three buttons on the right: Edit Post, Reply and Reply With Quote. These appear below EVERY one of your posts in this thread. I really don't know why you are unable to see those buttons, but for the sake of moving this along I will change the title for you today.

I suggest you think more carefully about thread titles in future to avoid this sort of thing.