vba advice how 2get a input box to quit by entering the word quit+ 4 a msgbox to disp

i have been doing the following coding which allows me to input through a input box 5 names this works however i would first of all like to enter the word QUIT before the 5th name is inputed so that i dont have to input anymore then the second thind i want to do is get a message box to appear after its has quit to show in numbers how many names have been put in for example if only two names have been put in before the word quit is entered i then would like a message box to appear with "2" in it. if anyone can help i would be so greatful. below is my coding


Private Sub pressHereToInputManufacturesNames_Click()

Dim manufacturersName1$
Dim manufacturersName2$
Dim manufacturersName3$
Dim manufacturersName4$
Dim manufacturersName5$
Dim i As Integer
Dim rRange As Excel.Range
Dim strtext$
Set rRange = Range("a1", Range("a1").End(xlUp))
Dim iTheRows As Long

'the below message box instructs the user to enter the word QUIT before they input a fifth
'manufacturers name the use of "&vbCrlf" in the coding forces a break in the line of the message

MsgBox "Please can you enter the word QUIT befor putting in 5 manufacturers names" & vbCrLf & "its up to you when you do it aslong as it is before the 5th name Thankyou"

With ActiveSheet
manufacturesName1 = InputBox("Manufactorsname 1")
.[a1].Value = manufacturesName1
manufacturesName2 = InputBox("Manufactorsname 2")
.[a2].Value = manufacturesName2
manufacturesName3 = InputBox("Manufactorsname 3")
.[a3].Value = manufacturesName3
manufacturesName4 = InputBox("Manufactorsname 4")
.[A4].Value = manufacturesName4
manufacturesName5 = InputBox("Manufactorsname 5")
.[a5].Value = manufacturesName5

End With

' this below section of coding selects the cells which the manufactures names have been inputted into

iTheRows = rRange.Rows.Count
[a1,a2,a3,a4,a5].Select

' the below section of coding makes another message box come up showing the manufactures names that have been inputted

'in here i want to i think put coding in to make it stop when the word quit is put into the input box

'then after this i want the message box to display as a interger how many names in total have been inputed into the cell range a1:a5


For i = 1 To iTheRows Step 1
strtext$ = strtext$ & ActiveCell.Value & vbCrLf
Next i
MsgBox strtext$


End Sub

One way:

Const maxManufacturers As Long = 5
Dim vResult As Variant
Dim manufacturersName(1 To maxManufacturers) As String
Dim i As Long
Dim numManufacturers As Long
Dim bCancelled As Boolean

With ActiveSheet
For i = LBound(manufacturersName) To UBound(manufacturersName)
Do
vResult = Application.InputBox( _
Prompt:="Manufacturer's Name " & i, _
Title:="Manufacturer's Names", _
Type:=2, _
Default:="Quit")
If vResult = False Then
bCancelled = True
Exit For
End If
If LCase(vResult) = "quit" Then Exit For
Loop Until Len(Trim(vResult)) > 0
manufacturersName(i) = Trim(vResult)
Next i
If Not bCancelled Then
numManufacturers = i - 1
With .Range("A1")
.Resize(maxManufacturers, 1).ClearContents
If numManufacturers > 0 Then _
.Resize(numManufacturers, 1).Value = _
Application.Transpose(manufacturersName)
End With
MsgBox numManufacturers & " names entered."
End If
End With

note that (a) you almost never need to select a range in order to use
it, and (b) using the Range objects directly (Range("A1")) is somewhat
more efficient than using the Evaluate Method ([a1])

In article <[email protected]>,
RELWOD85 <[email protected]>
wrote:

> i have been doing the following coding which allows me to input through
> a input box 5 names this works however i would first of all like to
> enter the word QUIT before the 5th name is inputed so that i dont have
> to input anymore then the second thind i want to do is get a message
> box to appear after its has quit to show in numbers how many names have
> been put in for example if only two names have been put in before the
> word quit is entered i then would like a message box to appear with "2"
> in it. if anyone can help i would be so greatful. below is my coding
>
>
> Private Sub pressHereToInputManufacturesNames_Click()
>
> Dim manufacturersName1$
> Dim manufacturersName2$
> Dim manufacturersName3$
> Dim manufacturersName4$
> Dim manufacturersName5$
> Dim i As Integer
> Dim rRange As Excel.Range
> Dim strtext$
> Set rRange = Range("a1", Range("a1").End(xlUp))
> Dim iTheRows As Long
>
> 'the below message box instructs the user to enter the word QUIT before
> they input a fifth
> 'manufacturers name the use of "&vbCrlf" in the coding forces a break
> in the line of the message
>
> MsgBox "Please can you enter the word QUIT befor putting in 5
> manufacturers names" & vbCrLf & "its up to you when you do it aslong as
> it is before the 5th name Thankyou"
>
> With ActiveSheet
> manufacturesName1 = InputBox("Manufactorsname 1")
> .[a1].Value = manufacturesName1
> manufacturesName2 = InputBox("Manufactorsname 2")
> .[a2].Value = manufacturesName2
> manufacturesName3 = InputBox("Manufactorsname 3")
> .[a3].Value = manufacturesName3
> manufacturesName4 = InputBox("Manufactorsname 4")
> .[A4].Value = manufacturesName4
> manufacturesName5 = InputBox("Manufactorsname 5")
> .[a5].Value = manufacturesName5
>
> End With
>
> ' this below section of coding selects the cells which the manufactures
> names have been inputted into
>
> iTheRows = rRange.Rows.Count
> [a1,a2,a3,a4,a5].Select
>
> ' the below section of coding makes another message box come up showing
> the manufactures names that have been inputted
>
> 'in here i want to i think put coding in to make it stop when the word
> quit is put into the input box
>
> 'then after this i want the message box to display as a interger how
> many names in total have been inputed into the cell range a1:a5
>
>
> For i = 1 To iTheRows Step 1
> strtext$ = strtext$ & ActiveCell.Value & vbCrLf
> Next i
> MsgBox strtext$
>
>
> End Sub

I started trying to add the "QUIT" option but as it requires a different
approach to entering the manufacturers I ended up with pretty much a whole
new Sub - hope you don't mind, it can at least show you some ideas:

Public Sub pressHereToInputManufacturesNames_Click()

Dim ManufacturersNames$(5)
Dim CountManufacturers As Integer
Dim i As Integer
Dim UserInput$
Dim iTheRows As Long

CountManufacturers = 0
UserInput$ = "OK"

While CountManufacturers <= 4 And UCase(UserInput$) <> "QUIT" And UserInput$
<> ""
UserInput$ = InputBox("Enter the Manufacturer's Name" & vbCrLf _
& "You may enter the word QUIT when you are finished" & vbCrLf _
& "You may press CANCEL to abort" & vbCrLf _
& CountManufacturers & " manufacturers entered so far")
If UserInput$ <> "QUIT" Then
CountManufacturers = CountManufacturers + 1
ManufacturersNames$(CountManufacturers) = UserInput$
End If
Wend
If UserInput$ = "" Then
MsgBox "CANCELLED: any entries will be ignored"
Exit Sub
End If

UserInput$ = ""
For i = 1 To CountManufacturers
ActiveSheet.Range("A" & i).Value = ManufacturersNames$(i)
UserInput$ = UserInput$ & ManufacturersNames$(i)
If i < CountManufacturers Then UserInput$ = UserInput$ & vbCrLf
Next i

MsgBox "You entered " & CountManufacturers & " Manufacturers:" & vbCrLf &
UserInput$

End Sub

--
- K Dales


"RELWOD85" wrote:

>
> i have been doing the following coding which allows me to input through
> a input box 5 names this works however i would first of all like to
> enter the word QUIT before the 5th name is inputed so that i dont have
> to input anymore then the second thind i want to do is get a message
> box to appear after its has quit to show in numbers how many names have
> been put in for example if only two names have been put in before the
> word quit is entered i then would like a message box to appear with "2"
> in it. if anyone can help i would be so greatful. below is my coding
>
>
> Private Sub pressHereToInputManufacturesNames_Click()
>
> Dim manufacturersName1$
> Dim manufacturersName2$
> Dim manufacturersName3$
> Dim manufacturersName4$
> Dim manufacturersName5$
> Dim i As Integer
> Dim rRange As Excel.Range
> Dim strtext$
> Set rRange = Range("a1", Range("a1").End(xlUp))
> Dim iTheRows As Long
>
> 'the below message box instructs the user to enter the word QUIT before
> they input a fifth
> 'manufacturers name the use of "&vbCrlf" in the coding forces a break
> in the line of the message
>
> MsgBox "Please can you enter the word QUIT befor putting in 5
> manufacturers names" & vbCrLf & "its up to you when you do it aslong as
> it is before the 5th name Thankyou"
>
> With ActiveSheet
> manufacturesName1 = InputBox("Manufactorsname 1")
> .[a1].Value = manufacturesName1
> manufacturesName2 = InputBox("Manufactorsname 2")
> .[a2].Value = manufacturesName2
> manufacturesName3 = InputBox("Manufactorsname 3")
> .[a3].Value = manufacturesName3
> manufacturesName4 = InputBox("Manufactorsname 4")
> .[A4].Value = manufacturesName4
> manufacturesName5 = InputBox("Manufactorsname 5")
> .[a5].Value = manufacturesName5
>
> End With
>
> ' this below section of coding selects the cells which the manufactures
> names have been inputted into
>
> iTheRows = rRange.Rows.Count
> [a1,a2,a3,a4,a5].Select
>
> ' the below section of coding makes another message box come up showing
> the manufactures names that have been inputted
>
> 'in here i want to i think put coding in to make it stop when the word
> quit is put into the input box
>
> 'then after this i want the message box to display as a interger how
> many names in total have been inputed into the cell range a1:a5
>
>
> For i = 1 To iTheRows Step 1
> strtext$ = strtext$ & ActiveCell.Value & vbCrLf
> Next i
> MsgBox strtext$
>
>
> End Sub
>
>
> --
> RELWOD85
> ------------------------------------------------------------------------
> RELWOD85's Profile: http://www.excelforum.com/member.php...o&userid=25753
> View this thread: http://www.excelforum.com/showthread...hreadid=391833
>
>

Using a loop is the best way, but here is a modification of your approach

Private Sub pressHereToInputManufacturesNames_Click()

Dim manufacturersName1$
Dim manufacturersName2$
Dim manufacturersName3$
Dim manufacturersName4$
Dim manufacturersName5$
Dim i As Integer
Dim rRange As Excel.Range
Dim strtext$
Set rRange = Range("a1", Range("a1").End(xlUp))
Dim iTheRows As Long
Dim sStr as String

'the below message box instructs the user to enter the word QUIT before they
input a fifth
'manufacturers name the use of "&vbCrlf" in the coding forces a break in the
line of the message

MsgBox "Please can you enter the word QUIT befor putting in 5 manufacturers
names" & vbCrLf & "its up to you when you do it as long as it is before the
5th name Thankyou"

With ActiveSheet
manufacturesName1 = InputBox("Manufactorsname 1")
If LCase(manufacturesName1) = "quit" Then

Else
[a1].Value = manufacturesName1
sStr = manufacturesName1 & vbNewLine
manufacturesName2 = InputBox("Manufactorsname 2")
If LCase(manufacturesName2) = "quit" Then
Else
sStr = sStr & manufacturesName2 & vbNewLine
[a2].Value = manufacturesName2
manufacturesName3 = InputBox("Manufactorsname 3")
If LCase(manufacturesName3) = "quit" Then
Else
sStr = sStr & manufacturesName3 & vbNewLine
[a3].Value = manufacturesName3
manufacturesName4 = InputBox("Manufactorsname 4")
If LCase(manufacturesName4) = "quit" Then
Else
sStr = sStr & manufacturesName4 & vbNewLine
[A4].Value = manufacturesName4
manufacturesName5 = InputBox("Manufactorsname 5")
If LCase(manufacturesName5) = "quit" Then
Else
sStr = sStr & manufacturesName5 & vbNewLine
[a5].Value = manufacturesName5
End If
End If
End If
End If
End If
If Len(sStr) > 0 Then
MsgBox Left(sStr, Len(sStr) - 2)
End If

End With

End Sub

--
Regards,
Tom Ogilvy


"RELWOD85" <[email protected]> wrote in
message news:[email protected]...
>
> i have been doing the following coding which allows me to input through
> a input box 5 names this works however i would first of all like to
> enter the word QUIT before the 5th name is inputed so that i dont have
> to input anymore then the second thind i want to do is get a message
> box to appear after its has quit to show in numbers how many names have
> been put in for example if only two names have been put in before the
> word quit is entered i then would like a message box to appear with "2"
> in it. if anyone can help i would be so greatful. below is my coding
>
>
> Private Sub pressHereToInputManufacturesNames_Click()
>
> Dim manufacturersName1$
> Dim manufacturersName2$
> Dim manufacturersName3$
> Dim manufacturersName4$
> Dim manufacturersName5$
> Dim i As Integer
> Dim rRange As Excel.Range
> Dim strtext$
> Set rRange = Range("a1", Range("a1").End(xlUp))
> Dim iTheRows As Long
>
> 'the below message box instructs the user to enter the word QUIT before
> they input a fifth
> 'manufacturers name the use of "&vbCrlf" in the coding forces a break
> in the line of the message
>
> MsgBox "Please can you enter the word QUIT befor putting in 5
> manufacturers names" & vbCrLf & "its up to you when you do it aslong as
> it is before the 5th name Thankyou"
>
> With ActiveSheet
> manufacturesName1 = InputBox("Manufactorsname 1")
> [a1].Value = manufacturesName1
> manufacturesName2 = InputBox("Manufactorsname 2")
> [a2].Value = manufacturesName2
> manufacturesName3 = InputBox("Manufactorsname 3")
> [a3].Value = manufacturesName3
> manufacturesName4 = InputBox("Manufactorsname 4")
> [A4].Value = manufacturesName4
> manufacturesName5 = InputBox("Manufactorsname 5")
> [a5].Value = manufacturesName5
>
> End With
>
> ' this below section of coding selects the cells which the manufactures
> names have been inputted into
>
> iTheRows = rRange.Rows.Count
> [a1,a2,a3,a4,a5].Select
>
> ' the below section of coding makes another message box come up showing
> the manufactures names that have been inputted
>
> 'in here i want to i think put coding in to make it stop when the word
> quit is put into the input box
>
> 'then after this i want the message box to display as a interger how
> many names in total have been inputed into the cell range a1:a5
>
>
> For i = 1 To iTheRows Step 1
> strtext$ = strtext$ & ActiveCell.Value & vbCrLf
> Next i
> MsgBox strtext$
>
>
> End Sub
>
>
> --
> RELWOD85
> ------------------------------------------------------------------------
> RELWOD85's Profile:
http://www.excelforum.com/member.php...o&userid=25753
> View this thread: http://www.excelforum.com/showthread...hreadid=391833
>