Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

UserForm Question

  Asked By: Demyan    Date: Aug 26    Category: MS Office    Views: 640
  

Trying to use User form to capture email address, then submit to
second worksheet, blankout input field then repeat if necessary. Put
in exit function by looking for "end".

Any suggestions on modifications to VBA code would be appreicated.


Sub Workbook_Open()
Dim MyInput
MyInput = InputBox("Enter your Email Address")
MsgBox ("Hello ") & MyInput & (" Thanks for entering the contest &
Good Luck")
Worksheets("WLWE").Range("A3:A3").Value = MyInput
If MyInput = "end" Then End
NextRow = Worksheets("Email_Data").Range("A65536").End(xlUp).Row + 1
Worksheets("Email_Data").Cells(NextRow, 1).Resize.Value = Array
(Worksheets("WLWE").Range("A3").Value, Worksheets("WLWE").Range
("A3").Value)
Worksheets("WLWE").Range("A3").Value = ""
Workbook_Open
End Sub

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Daniel Evans     Answered On: Aug 26

You don't need the intervening sheet WLWE at all, you do no checks on
it before deleting the data there so:

Private Sub Workbook_Open()
Dim MyInput
Do
NextRow = Worksheets("Email_Data").Range("A65536").End(xlUp).Row + 1
MyInput = InputBox("Enter your Email Address")
If UCase(MyInput) <> "END" Then
MsgBox "Hello " & MyInput & " Thanks for entering  the contest & Good Luck"
Worksheets("Email_Data").Cells(NextRow, 1) = MyInput
End If
Loop Until UCase(MyInput) = "END"
End Sub

A formal Do Loop is in there instead of just recalling the macro.
The UCase bits cater for any combination of upper and lower case
letters making up the word 'end'.
Since you haven't declared NextRow, you don't really need to declare
MyInput either (ie. the Dim MyInput line can be left out).
One or two of the lines above may have been wordwrapped so you'll have
to restore it/them.

 
Didn't find what you were looking for? Find more on UserForm Question Or get search suggestion and latest updates.




Tagged: