Hi all,
Sorry for two post in one day but I'm on a roll so thought I should ask while I am still motivated
Not 100% sure its possible, but I would like to copy and paste a value from a text box onto a running list in a sheet called "Quote" when I double click the row, the list starts at B16.
The macro I used to use is below, its also asks for a quantity and adds it to the next cell (C16), but this was kindly donated by a member here and was not designed to do what I now ask of it, somehow it did run once on double click but I could not find any output and now it hangs on Set Btn = ActiveSheet.Buttons(Application.Caller)
Any advice appreciated.
Private Sub ListBox1_Click()
Dim Btn As Object
Dim n As Long, r As Long
Dim QuantityQuery
Dim Choose As String
Set Btn = ActiveSheet.Buttons(Application.Caller)
r = Btn.TopLeftCell.Row
n = Cells(Rows.Count, "D").End(xlUp).Row - 5 + 1
Range("D5").Offset(n, 0).Value = Cells(r, "D").Text
n = Cells(Rows.Count, "I").End(xlUp).Row - 5 + 1
check:
With Sheets("Quote").Range("B16").Offset(n, 0)
QuantityQuery = .Value
If QuantityQuery = vbNullString Then
QuantityQuery = Application.InputBox("Please Enter Quantity", "ITEM QUANTITY", , , , , , Type:=1)
If QuantityQuery < 0 Then
MsgBox "Number is not valid", , "Microsoft Office Excel"
GoTo check
End If
End If
If VarType(QuantityQuery) = vbBoolean Then
Choose = MsgBox("-YOU PRESSED CANCEL- Are You Sure You Wish To Continue? ", vbYesNo, "WARNING")
If Choose = vbYes Then
Cells(Cells(Rows.Count, "D").End(xlUp).Row, "D").Value = ""
Else
If Choose = vbNo Then
GoTo check
End If
End If
Else:
.Value = QuantityQuery
End If
End With
End Sub
Bookmarks