Inputbox loop

Question

I have an input box that I am using to enter data onto another sheet which works fine.

What I have found to be a slight problem is that after I enter data that the input box closes. I would like for the input box to remain open (to continue entering data) until I cancel/close it.

One other thing, if when the first dialogue box comes up and I select yes, when the input box comes up if I choose to cancel, still makes an entry which if I cancel it at the input box stage, I want no entry.

Thanks and best Denny

Private Sub Label1_Click()

Dim sh As Worksheet answer = MsgBox("Do you want to add a new location?", vbYesNo, "Add a new location")

If answer = vbYes Then Set sh = Worksheets("location") nextrow = Application.WorksheetFunction.CountA(sh.Range("a:a")) + 1 sh.Cells(nextrow, 2) = InputBox("Add a new location") sh.Cells(nextrow, 1) = sh.Range("z1") + 1 Exit Sub ElseIf answer = vbNo Then Exit Sub

End If End Sub

Answer

Denny,

Private Sub Label1_Click()

Dim sh As Worksheet Dim ans as String Dim answer as Long answer = MsgBox("Do you want to add a new location?", vbYesNo, "Add a new location")

If answer = vbYes Then Set sh = Worksheets("location") nextrow = Application.WorksheetFunction.CountA(sh.Range("a:a")) + 1 ans = InputBox("Add a new location") if len(trim(ans)) > 0 then

sh.Cells(nextrow, 2) = ans
sh.Cells(nextrow, 1) = sh.Range("z1") + 1

end if Exit Sub ElseIf answer = vbNo Then Exit Sub

End If End Sub

So we receive the results of the inputbox in a variable and then use the value of that variable to make a decision. If the ans contains a null string, then we know the user has either hit cancel or put nothing in the inputbox and clicked OK. In either case, we would react the same, I would think, and treat it as a cancel.

the inputbox can't stay up. When you click cancel or OK, it automatically drop. You could immediately show another inputbox. However, in this particular piece of code I don't see where you would want the inputbox to stay up.

If you really need an input box to stay up, you can make a userform that looks like an inputbox. Just size the userform to be a small size and put a textbox and two command buttons on it.

If you need help with that, you can contact me directly.