VBA code, double click event

[stuartchaffey]

Hi all,

I have a list box on a userform, the userform loads values from a sheet. When i double click on a listbox value it runs a small bit of code. Can i make it loop so it double clicks the first value in the listbox, then carries on until it reaches the last value?

Thanks in advance

 

[Micron]

Are you saying that you want some sort of user action to select all the list rows, or actually repeat for all rows whatever your code does for the one row that was double clicked? If the latter, do whatever you're doing inside of a loop that runs from 0 to mylistbox.listcount -1?

 

[stuartchaffey]

Thank you for your quick response,

I want to press a command button it will double click the first value in the list box, down to the last. I hope this makes sense. Im not the best at vba so not sure how to do it.

 

[Micron]

While double click is a user action, it is also an event that runs code. I don't see the point of double clicking each item in code, assuming you can even do that. AFAIK, you'd need code for the dbl click event and then have to call that repeatedly from button click code, which makes no sense.

Basically you just repeated what you've already asked for. Maybe if you posted your code it will help. Please use code tags (vba button on posting toolbar) to maintain indentation in your code.

 

[stuartchaffey]

Thanks again for your fast response. When i double click the number in the list box it runs the following code:

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
BARCODEPRINTING.TXT1.Value = Me.ListBox1.Value
With Me.TXT1
.SelStart = 0
.SelLength = Len(.Text)
.Copy
End With
Sheets("BCWOB").Range("B1") = TXT1.Value
Sheets("BCWOB").Range("C1") = "B"
Sheet35.PrintOut
PRINTING.Show
End Sub

so when i click the green button above the list box, i want it to double click the first value and work through.

Thanks again in advance

 

[Micron]

Please use code tags (vba button on posting toolbar) to maintain indentation in your code.

Sometimes when a member ignores that request I don't reply. Today is Easter Sunday, so...

Your button click code event might look like the following. I don't see the sense in copying the listbox value into a textbox and then from the textbox into a sheet so I eliminated that part. I'm not seeing the connection between what happens to BCWOB and Sheet35. You might find that this runs too fast for printing. In that case, a pause would need to be introduced.

Dim i As Integer
For i = 0 To Me.Listbox1.Listcount -1
   Sheets("BCWOB").Range("B1") = Me.Listbox1.List(i)
   Sheets("BCWOB").Range("C1") = "B"
   Sheet35.PrintOut
   PRINTING.Show
Next

EDIT - if you have other event code, changing sheet values might cause it to run in a continuous loop. In that case, events should be turned off.

 

[stuartchaffey]

Thank you again for your quick reply. I didnt know how to use code tags (vba button on posting toolbar) to maintain indentation in your code, so apologies for that. The code works perfectly, except like you say it runs in a continuous loop, how do i turn off events like you suggested?. Thanks again

 

[Micron]

I click the button and put the cursor in between the resulting tags then paste. Or paste indented code, select and click code tags button.
Try this

Dim i As Integer

On Error GoTo errHandler
Application.EnableEvents = False
For i = 0 To Me.ListBox1.ListCount - 1
   Sheets("BCWOB").Range("B1") = Me.ListBox1.List(i)
   Sheets("BCWOB").Range("C1") = "B"
   Sheet35.PrintOut
   PRINTING.Show
Next

exitHere:
Application.EnableEvents= True
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere