Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 throug a input box 5 names this works however i would first of all like t enter the word QUIT before the 5th name is inputed so that i dont hav to input anymore then the second thind i want to do is get a messag box to appear after its has quit to show in numbers how many names hav been put in for example if only two names have been put in before th 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 befor they input a fifth 'manufacturers name the use of "&vbCrlf" in the coding forces a brea in the line of the message MsgBox "Please can you enter the word QUIT befor putting in manufacturers names" & vbCrLf & "its up to you when you do it aslong a 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 manufacture 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 showin the manufactures names that have been inputted 'in here i want to i think put coding in to make it stop when the wor quit is put into the input box 'then after this i want the message box to display as a interger ho 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 Su -- RELWOD8 ----------------------------------------------------------------------- RELWOD85's Profile: http://www.excelforum.com/member.php...fo&userid=2575 View this thread: http://www.excelforum.com/showthread.php?threadid=39183 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba advice how 2get a input box to quit by entering the word quit+ 4 a msgbox to disp
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 , 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba advice how 2get a input box to quit by entering the word quit+
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba advice how 2get a input box to quit by entering the word quit+ 4 a msgbox to disp
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Quit | Excel Programming | |||
Application.Quit | Excel Programming | |||
Can't get Excel to quit | Excel Programming | |||
Excel won't quit | Excel Programming | |||
Quit Application | Excel Programming |