| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Until Inputbox = loop count
Hi all,
I cannot work out why this isn't working. The user inputs a number from 1 to 4 into an inputbox, and I want the macro to loop for this number of times (if 1 is entered, an if statement avoids this loop). My code is as below, but the loop just keeps on going for any number entered... Dim Message, Title, Default Message = "Please enter the number of the current quarter (1 to 4)" Title = "Enter Quarter" Default = "1" qvalue = InputBox(Message, Title, Default) Do Until QCount = qvalue QCount = QCount + 1 'my code here Loop Any ideas? Thanks, Ewan. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Do Until Inputbox = loop count
Hi,
Your inputbox is returning a text string so qvalue will never equal qcoount hence the endless loop. Try this:- Sub sonic() Dim Message, Title, Default Dim qvalue As Integer Message = "Please enter the number of the current quarter (1 to 4)" Title = "Enter Quarter" Default = 1 qvalue = InputBox(Message, Title, Default) Do Until qcount = qvalue qcount = qcount + 1 'my code here Loop MsgBox qcount End Sub Mike "ewan7279" wrote: Hi all, I cannot work out why this isn't working. The user inputs a number from 1 to 4 into an inputbox, and I want the macro to loop for this number of times (if 1 is entered, an if statement avoids this loop). My code is as below, but the loop just keeps on going for any number entered... Dim Message, Title, Default Message = "Please enter the number of the current quarter (1 to 4)" Title = "Enter Quarter" Default = "1" qvalue = InputBox(Message, Title, Default) Do Until QCount = qvalue QCount = QCount + 1 'my code here Loop Any ideas? Thanks, Ewan. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Do Until Inputbox = loop count
Hi Mike,
Sorry, I should have included that I have already declared qvalue as an integer variable and it still does not work. The inputbox value (qvalue) works in an if statement before the loop, and there is a validation also to check the value entered does not exceed 4, with a msgbox resulting if it does. It just doesn't work in this loop... Any ideas? Thanks. "Mike H" wrote: Hi, Your inputbox is returning a text string so qvalue will never equal qcoount hence the endless loop. Try this:- Sub sonic() Dim Message, Title, Default Dim qvalue As Integer Message = "Please enter the number of the current quarter (1 to 4)" Title = "Enter Quarter" Default = 1 qvalue = InputBox(Message, Title, Default) Do Until qcount = qvalue qcount = qcount + 1 'my code here Loop MsgBox qcount End Sub Mike "ewan7279" wrote: Hi all, I cannot work out why this isn't working. The user inputs a number from 1 to 4 into an inputbox, and I want the macro to loop for this number of times (if 1 is entered, an if statement avoids this loop). My code is as below, but the loop just keeps on going for any number entered... Dim Message, Title, Default Message = "Please enter the number of the current quarter (1 to 4)" Title = "Enter Quarter" Default = "1" qvalue = InputBox(Message, Title, Default) Do Until QCount = qvalue QCount = QCount + 1 'my code here Loop Any ideas? Thanks, Ewan. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Do Until Inputbox = loop count
Hi,
Then you are going to have to clarify what you mean by It just doesn't work in this loop... The code I posted works, if your doesnt then something in your code may be changing the value of qvalue so that it and Qcount can neve be equal. Mike "ewan7279" wrote: Hi Mike, Sorry, I should have included that I have already declared qvalue as an integer variable and it still does not work. The inputbox value (qvalue) works in an if statement before the loop, and there is a validation also to check the value entered does not exceed 4, with a msgbox resulting if it does. It just doesn't work in this loop... Any ideas? Thanks. "Mike H" wrote: Hi, Your inputbox is returning a text string so qvalue will never equal qcoount hence the endless loop. Try this:- Sub sonic() Dim Message, Title, Default Dim qvalue As Integer Message = "Please enter the number of the current quarter (1 to 4)" Title = "Enter Quarter" Default = 1 qvalue = InputBox(Message, Title, Default) Do Until qcount = qvalue qcount = qcount + 1 'my code here Loop MsgBox qcount End Sub Mike "ewan7279" wrote: Hi all, I cannot work out why this isn't working. The user inputs a number from 1 to 4 into an inputbox, and I want the macro to loop for this number of times (if 1 is entered, an if statement avoids this loop). My code is as below, but the loop just keeps on going for any number entered... Dim Message, Title, Default Message = "Please enter the number of the current quarter (1 to 4)" Title = "Enter Quarter" Default = "1" qvalue = InputBox(Message, Title, Default) Do Until QCount = qvalue QCount = QCount + 1 'my code here Loop Any ideas? Thanks, Ewan. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Do Until Inputbox = loop count
Dim Message, Title, Default
Message = "Please enter the number of the current quarter (1 to 4)" Title = "Enter Quarter" Default = "1" Do qvalue = val(InputBox(Message, Title, Default)) Loop while (qvalue < 1) and (qvalue 4) 'my code here "ewan7279" wrote: Hi Mike, Sorry, I should have included that I have already declared qvalue as an integer variable and it still does not work. The inputbox value (qvalue) works in an if statement before the loop, and there is a validation also to check the value entered does not exceed 4, with a msgbox resulting if it does. It just doesn't work in this loop... Any ideas? Thanks. "Mike H" wrote: Hi, Your inputbox is returning a text string so qvalue will never equal qcoount hence the endless loop. Try this:- Sub sonic() Dim Message, Title, Default Dim qvalue As Integer Message = "Please enter the number of the current quarter (1 to 4)" Title = "Enter Quarter" Default = 1 qvalue = InputBox(Message, Title, Default) Do Until qcount = qvalue qcount = qcount + 1 'my code here Loop MsgBox qcount End Sub Mike "ewan7279" wrote: Hi all, I cannot work out why this isn't working. The user inputs a number from 1 to 4 into an inputbox, and I want the macro to loop for this number of times (if 1 is entered, an if statement avoids this loop). My code is as below, but the loop just keeps on going for any number entered... Dim Message, Title, Default Message = "Please enter the number of the current quarter (1 to 4)" Title = "Enter Quarter" Default = "1" qvalue = InputBox(Message, Title, Default) Do Until QCount = qvalue QCount = QCount + 1 'my code here Loop Any ideas? Thanks, Ewan. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Do Until Inputbox = loop count
Hi Mike,
I've posted the section of code below so you can check if I am overlooking something. Everything works until the loop is entered, from where it never exits. 'On Error GoTo ErrorHandler ActiveSheet.Unprotect ActiveWorkbook.Unprotect Dim deptsheet, homefile, Qbook, Q1sheet, Q2sheet, Q3sheet, Q4sheet As Variant Dim qvalue, QCount, sheetcount As Integer homefile = ActiveWorkbook.Name Dim Message, Title, Default Message = "Please enter the number of the current quarter (1 to 4)" Title = "Enter Quarter" Default = "1" qvalue = InputBox(Message, Title, Default) If qvalue 4 Then MsgBox "Sorry, incorrect quarter entered. Please try again", vbOKOnly, "Error!!" Exit Sub Else If qvalue = 1 Then sheetcount = Workbooks(homefile).Sheets.Count Application.ScreenUpdating = True MsgBox "Please select the Q" & qvalue & " file", vbOKOnly, "Select Q" & qvalue & " File" Qbook = Application.GetOpenFilename() a = MsgBox("Open " & Qbook & "?", vbYesNoCancel, "Open Q" & qvalue & " file") If a = vbNo Then 'GoTo ErrorHandler Else If a = vbCancel Then 'GoTo ErrorHandler End If End If Application.ScreenUpdating = False Workbooks.OpenText Qbook Qbook = ActiveWorkbook.Name ActiveWorkbook.Unprotect Sheets(1).Select deptsheet = ActiveSheet.Name Windows(Qbook).Activate Sheets(deptsheet).Select Sheets(deptsheet).Copy After:=Workbooks(homefile).Sheets(sheetcount) ActiveSheet.Name = "Dept Card Q" & qvalue Dim Links As Variant Dim i As Integer ActiveSheet.Unprotect With ActiveWorkbook Links = .LinkSources(xlExcelLinks) If Not IsEmpty(Links) Then For i = 1 To UBound(Links) .BreakLink Links(i), xlLinkTypeExcelLinks Next i End If End With ActiveSheet.Protect Workbooks(Qbook).Close savechanges:=False Workbooks(homefile).Activate 'Sheets(1).Delete Else QCount = 0 Do Until QCount = qvalue QCount = QCount + 1 sheetcount = Workbooks(homefile).Sheets.Count Application.ScreenUpdating = True MsgBox "Please select the Q" & QCount & " file", vbOKOnly, "Select Q" & QCount & " File" Qbook = Application.GetOpenFilename() a = MsgBox("Open " & Qbook & "?", vbYesNoCancel, "Open Q" & QCount & " file") If a = vbNo Then 'GoTo ErrorHandler Else If a = vbCancel Then 'GoTo ErrorHandler End If End If Application.ScreenUpdating = False Workbooks.OpenText Qbook Qbook = ActiveWorkbook.Name ActiveWorkbook.Unprotect Sheets(1).Select deptsheet = ActiveSheet.Name Windows(Qbook).Activate Sheets(deptsheet).Select Sheets(deptsheet).Copy After:=Workbooks(homefile).Sheets(sheetcount) ActiveSheet.Name = "Dept Card Q" & QCount ActiveSheet.Unprotect With ActiveWorkbook Links = .LinkSources(xlExcelLinks) If Not IsEmpty(Links) Then For i = 1 To UBound(Links) .BreakLink Links(i), xlLinkTypeExcelLinks Next i End If End With ActiveSheet.Protect Workbooks(Qbook).Close savechanges:=False Loop End If End If etc etc etc "Mike H" wrote: Hi, Then you are going to have to clarify what you mean by It just doesn't work in this loop... The code I posted works, if your doesnt then something in your code may be changing the value of qvalue so that it and Qcount can neve be equal. Mike "ewan7279" wrote: Hi Mike, Sorry, I should have included that I have already declared qvalue as an integer variable and it still does not work. The inputbox value (qvalue) works in an if statement before the loop, and there is a validation also to check the value entered does not exceed 4, with a msgbox resulting if it does. It just doesn't work in this loop... Any ideas? Thanks. "Mike H" wrote: Hi, Your inputbox is returning a text string so qvalue will never equal qcoount hence the endless loop. Try this:- Sub sonic() Dim Message, Title, Default Dim qvalue As Integer Message = "Please enter the number of the current quarter (1 to 4)" Title = "Enter Quarter" Default = 1 qvalue = InputBox(Message, Title, Default) Do Until qcount = qvalue qcount = qcount + 1 'my code here Loop MsgBox qcount End Sub Mike "ewan7279" wrote: Hi all, I cannot work out why this isn't working. The user inputs a number from 1 to 4 into an inputbox, and I want the macro to loop for this number of times (if 1 is entered, an if statement avoids this loop). My code is as below, but the loop just keeps on going for any number entered... Dim Message, Title, Default Message = "Please enter the number of the current quarter (1 to 4)" Title = "Enter Quarter" Default = "1" qvalue = InputBox(Message, Title, Default) Do Until QCount = qvalue QCount = QCount + 1 'my code here Loop Any ideas? Thanks, Ewan. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Do Until Inputbox = loop count
Sub ABC()
Dim QCount As Long, qValue As Long Dim Ans As Variant Dim Message, Title, Default Message = "Please enter the number of the current quarter (1 to 4)" Title = "Enter Quarter" Default = "1" Ans = Application.InputBox(Message, _ Title, Default, Type:=2) If TypeName(Ans) = "Boolean" Then Exit Sub qValue = Int(Ans) If qValue < 1 Or qValue 4 Then MsgBox "Must be between 1 and 4 inclusive" Exit Sub End If QCount = 0 Do QCount = QCount + 1 Debug.Print QCount 'my code here Loop Until QCount = qValue End Sub worked for me. -- Regards, Tom Ogilvy "ewan7279" wrote: Hi all, I cannot work out why this isn't working. The user inputs a number from 1 to 4 into an inputbox, and I want the macro to loop for this number of times (if 1 is entered, an if statement avoids this loop). My code is as below, but the loop just keeps on going for any number entered... Dim Message, Title, Default Message = "Please enter the number of the current quarter (1 to 4)" Title = "Enter Quarter" Default = "1" qvalue = InputBox(Message, Title, Default) Do Until QCount = qvalue QCount = QCount + 1 'my code here Loop Any ideas? Thanks, Ewan. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Do Until Inputbox = loop count
Thanks Joel!!
"Joel" wrote: Dim Message, Title, Default Message = "Please enter the number of the current quarter (1 to 4)" Title = "Enter Quarter" Default = "1" Do qvalue = val(InputBox(Message, Title, Default)) Loop while (qvalue < 1) and (qvalue 4) 'my code here "ewan7279" wrote: Hi Mike, Sorry, I should have included that I have already declared qvalue as an integer variable and it still does not work. The inputbox value (qvalue) works in an if statement before the loop, and there is a validation also to check the value entered does not exceed 4, with a msgbox resulting if it does. It just doesn't work in this loop... Any ideas? Thanks. "Mike H" wrote: Hi, Your inputbox is returning a text string so qvalue will never equal qcoount hence the endless loop. Try this:- Sub sonic() Dim Message, Title, Default Dim qvalue As Integer Message = "Please enter the number of the current quarter (1 to 4)" Title = "Enter Quarter" Default = 1 qvalue = InputBox(Message, Title, Default) Do Until qcount = qvalue qcount = qcount + 1 'my code here Loop MsgBox qcount End Sub Mike "ewan7279" wrote: Hi all, I cannot work out why this isn't working. The user inputs a number from 1 to 4 into an inputbox, and I want the macro to loop for this number of times (if 1 is entered, an if statement avoids this loop). My code is as below, but the loop just keeps on going for any number entered... Dim Message, Title, Default Message = "Please enter the number of the current quarter (1 to 4)" Title = "Enter Quarter" Default = "1" qvalue = InputBox(Message, Title, Default) Do Until QCount = qvalue QCount = QCount + 1 'my code here Loop Any ideas? Thanks, Ewan. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Do Until Inputbox = loop count
It should be an or instead of an and
Loop while (qvalue < 1) or (qvalue 4) "ewan7279" wrote: Thanks Joel!! "Joel" wrote: Dim Message, Title, Default Message = "Please enter the number of the current quarter (1 to 4)" Title = "Enter Quarter" Default = "1" Do qvalue = val(InputBox(Message, Title, Default)) Loop while (qvalue < 1) and (qvalue 4) 'my code here "ewan7279" wrote: Hi Mike, Sorry, I should have included that I have already declared qvalue as an integer variable and it still does not work. The inputbox value (qvalue) works in an if statement before the loop, and there is a validation also to check the value entered does not exceed 4, with a msgbox resulting if it does. It just doesn't work in this loop... Any ideas? Thanks. "Mike H" wrote: Hi, Your inputbox is returning a text string so qvalue will never equal qcoount hence the endless loop. Try this:- Sub sonic() Dim Message, Title, Default Dim qvalue As Integer Message = "Please enter the number of the current quarter (1 to 4)" Title = "Enter Quarter" Default = 1 qvalue = InputBox(Message, Title, Default) Do Until qcount = qvalue qcount = qcount + 1 'my code here Loop MsgBox qcount End Sub Mike "ewan7279" wrote: Hi all, I cannot work out why this isn't working. The user inputs a number from 1 to 4 into an inputbox, and I want the macro to loop for this number of times (if 1 is entered, an if statement avoids this loop). My code is as below, but the loop just keeps on going for any number entered... Dim Message, Title, Default Message = "Please enter the number of the current quarter (1 to 4)" Title = "Enter Quarter" Default = "1" qvalue = InputBox(Message, Title, Default) Do Until QCount = qvalue QCount = QCount + 1 'my code here Loop Any ideas? Thanks, Ewan. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Do Until Inputbox = loop count
If I just copy your loop code:
Sub EFG() qValue = 4 QCount = 0 Do Until QCount = qValue QCount = QCount + 1 Debug.Print QCount Loop End Sub it works fine, so there doesn't appear to be a problem in the loop itself. I would suggest doing the check at the end of the loop so you don't have to duplicate your code in two places. To debug, just put a msgbox inside the loop to display the values of interest (QCount and qValue) I see you have errhandler instructions commented out - if you are testing with no error handling and getting the problem, then it probably isn't your error handling, but if the error handling is being used in your testing, it could be a contributor. -- Regards, Tom Ogilvy "ewan7279" wrote: Hi Mike, I've posted the section of code below so you can check if I am overlooking something. Everything works until the loop is entered, from where it never exits. 'On Error GoTo ErrorHandler ActiveSheet.Unprotect ActiveWorkbook.Unprotect Dim deptsheet, homefile, Qbook, Q1sheet, Q2sheet, Q3sheet, Q4sheet As Variant Dim qvalue, QCount, sheetcount As Integer homefile = ActiveWorkbook.Name Dim Message, Title, Default Message = "Please enter the number of the current quarter (1 to 4)" Title = "Enter Quarter" Default = "1" qvalue = InputBox(Message, Title, Default) If qvalue 4 Then MsgBox "Sorry, incorrect quarter entered. Please try again", vbOKOnly, "Error!!" Exit Sub Else If qvalue = 1 Then sheetcount = Workbooks(homefile).Sheets.Count Application.ScreenUpdating = True MsgBox "Please select the Q" & qvalue & " file", vbOKOnly, "Select Q" & qvalue & " File" Qbook = Application.GetOpenFilename() a = MsgBox("Open " & Qbook & "?", vbYesNoCancel, "Open Q" & qvalue & " file") If a = vbNo Then 'GoTo ErrorHandler Else If a = vbCancel Then 'GoTo ErrorHandler End If End If Application.ScreenUpdating = False Workbooks.OpenText Qbook Qbook = ActiveWorkbook.Name ActiveWorkbook.Unprotect Sheets(1).Select deptsheet = ActiveSheet.Name Windows(Qbook).Activate Sheets(deptsheet).Select Sheets(deptsheet).Copy After:=Workbooks(homefile).Sheets(sheetcount) ActiveSheet.Name = "Dept Card Q" & qvalue Dim Links As Variant Dim i As Integer ActiveSheet.Unprotect With ActiveWorkbook Links = .LinkSources(xlExcelLinks) If Not IsEmpty(Links) Then For i = 1 To UBound(Links) .BreakLink Links(i), xlLinkTypeExcelLinks Next i End If End With ActiveSheet.Protect Workbooks(Qbook).Close savechanges:=False Workbooks(homefile).Activate 'Sheets(1).Delete Else QCount = 0 Do Until QCount = qvalue QCount = QCount + 1 sheetcount = Workbooks(homefile).Sheets.Count Application.ScreenUpdating = True MsgBox "Please select the Q" & QCount & " file", vbOKOnly, "Select Q" & QCount & " File" Qbook = Application.GetOpenFilename() a = MsgBox("Open " & Qbook & "?", vbYesNoCancel, "Open Q" & QCount & " file") If a = vbNo Then 'GoTo ErrorHandler Else If a = vbCancel Then 'GoTo ErrorHandler End If End If Application.ScreenUpdating = False Workbooks.OpenText Qbook Qbook = ActiveWorkbook.Name ActiveWorkbook.Unprotect Sheets(1).Select deptsheet = ActiveSheet.Name Windows(Qbook).Activate Sheets(deptsheet).Select Sheets(deptsheet).Copy After:=Workbooks(homefile).Sheets(sheetcount) ActiveSheet.Name = "Dept Card Q" & QCount ActiveSheet.Unprotect With ActiveWorkbook Links = .LinkSources(xlExcelLinks) If Not IsEmpty(Links) Then For i = 1 To UBound(Links) .BreakLink Links(i), xlLinkTypeExcelLinks Next i End If End With ActiveSheet.Protect Workbooks(Qbook).Close savechanges:=False Loop End If End If etc etc etc "Mike H" wrote: Hi, Then you are going to have to clarify what you mean by It just doesn't work in this loop... The code I posted works, if your doesnt then something in your code may be changing the value of qvalue so that it and Qcount can neve be equal. Mike "ewan7279" wrote: Hi Mike, Sorry, I should have included that I have already declared qvalue as an integer variable and it still does not work. The inputbox value (qvalue) works in an if statement before the loop, and there is a validation also to check the value entered does not exceed 4, with a msgbox resulting if it does. It just doesn't work in this loop... Any ideas? Thanks. "Mike H" wrote: Hi, Your inputbox is returning a text string so qvalue will never equal qcoount hence the endless loop. Try this:- Sub sonic() Dim Message, Title, Default Dim qvalue As Integer Message = "Please enter the number of the current quarter (1 to 4)" Title = "Enter Quarter" Default = 1 qvalue = InputBox(Message, Title, Default) Do Until qcount = qvalue qcount = qcount + 1 'my code here Loop MsgBox qcount End Sub Mike "ewan7279" wrote: Hi all, I cannot work out why this isn't working. The user inputs a number from 1 to 4 into an inputbox, and I want the macro to loop for this number of times (if 1 is entered, an if statement avoids this loop). My code is as below, but the loop just keeps on going for any number entered... Dim Message, Title, Default Message = "Please enter the number of the current quarter (1 to 4)" Title = "Enter Quarter" Default = "1" qvalue = InputBox(Message, Title, Default) Do Until QCount = qvalue QCount = QCount + 1 'my code here Loop Any ideas? Thanks, Ewan. |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Do Until Inputbox = loop count
Hi Tom,
Thanks for your reply. I had tried the msgbox method, and it showed that Qcount was increasing by 1 each time, even though qvalue remained at the value entered by the user, indicating qvalue was not recognised as an integer but Qcount was; and thus Excel was comparing apples to pears. What I did notice was when I hovered the mouse cursor over qvalue and Qcount in the code during 'break mode' (is this what it's called?) the value for Qcount would show as a number e.g. 2, but the value for qvalue would show as a number in speech marks e.g. "2". This has been corrected by adding 'Val' before the inputbox part of the code. My final solution is as below: Message = "Please enter the number of the current quarter (1 to 4)" Title = "Enter Quarter" Default = "1" ' Display message, title, and default value. qvalue = Val(InputBox(Message, Title, Default)) If qvalue < 1 Or qvalue 4 Then GoTo ErrorHandler 'which says try again - must be quarter 1 to 4 Else If qvalue = 1 Then 'some code here Else 'more than one quarter, so create sheets etc for each quarter 'up to the value entered by the user Do Until QCount = qvalue QCount = QCount + 1 'more code here Loop End If End If Thanks again for your input, Ewan. "Tom Ogilvy" wrote: If I just copy your loop code: Sub EFG() qValue = 4 QCount = 0 Do Until QCount = qValue QCount = QCount + 1 Debug.Print QCount Loop End Sub it works fine, so there doesn't appear to be a problem in the loop itself. I would suggest doing the check at the end of the loop so you don't have to duplicate your code in two places. To debug, just put a msgbox inside the loop to display the values of interest (QCount and qValue) I see you have errhandler instructions commented out - if you are testing with no error handling and getting the problem, then it probably isn't your error handling, but if the error handling is being used in your testing, it could be a contributor. -- Regards, Tom Ogilvy "ewan7279" wrote: Hi Mike, I've posted the section of code below so you can check if I am overlooking something. Everything works until the loop is entered, from where it never exits. 'On Error GoTo ErrorHandler ActiveSheet.Unprotect ActiveWorkbook.Unprotect Dim deptsheet, homefile, Qbook, Q1sheet, Q2sheet, Q3sheet, Q4sheet As Variant Dim qvalue, QCount, sheetcount As Integer homefile = ActiveWorkbook.Name Dim Message, Title, Default Message = "Please enter the number of the current quarter (1 to 4)" Title = "Enter Quarter" Default = "1" qvalue = InputBox(Message, Title, Default) If qvalue 4 Then MsgBox "Sorry, incorrect quarter entered. Please try again", vbOKOnly, "Error!!" Exit Sub Else If qvalue = 1 Then sheetcount = Workbooks(homefile).Sheets.Count Application.ScreenUpdating = True MsgBox "Please select the Q" & qvalue & " file", vbOKOnly, "Select Q" & qvalue & " File" Qbook = Application.GetOpenFilename() a = MsgBox("Open " & Qbook & "?", vbYesNoCancel, "Open Q" & qvalue & " file") If a = vbNo Then 'GoTo ErrorHandler Else If a = vbCancel Then 'GoTo ErrorHandler End If End If Application.ScreenUpdating = False Workbooks.OpenText Qbook Qbook = ActiveWorkbook.Name ActiveWorkbook.Unprotect Sheets(1).Select deptsheet = ActiveSheet.Name Windows(Qbook).Activate Sheets(deptsheet).Select Sheets(deptsheet).Copy After:=Workbooks(homefile).Sheets(sheetcount) ActiveSheet.Name = "Dept Card Q" & qvalue Dim Links As Variant Dim i As Integer ActiveSheet.Unprotect With ActiveWorkbook Links = .LinkSources(xlExcelLinks) If Not IsEmpty(Links) Then For i = 1 To UBound(Links) .BreakLink Links(i), xlLinkTypeExcelLinks Next i End If End With ActiveSheet.Protect Workbooks(Qbook).Close savechanges:=False Workbooks(homefile).Activate 'Sheets(1).Delete Else QCount = 0 Do Until QCount = qvalue QCount = QCount + 1 sheetcount = Workbooks(homefile).Sheets.Count Application.ScreenUpdating = True MsgBox "Please select the Q" & QCount & " file", vbOKOnly, "Select Q" & QCount & " File" Qbook = Application.GetOpenFilename() a = MsgBox("Open " & Qbook & "?", vbYesNoCancel, "Open Q" & QCount & " file") If a = vbNo Then 'GoTo ErrorHandler Else If a = vbCancel Then 'GoTo ErrorHandler End If End If Application.ScreenUpdating = False Workbooks.OpenText Qbook Qbook = ActiveWorkbook.Name ActiveWorkbook.Unprotect Sheets(1).Select deptsheet = ActiveSheet.Name Windows(Qbook).Activate Sheets(deptsheet).Select Sheets(deptsheet).Copy After:=Workbooks(homefile).Sheets(sheetcount) ActiveSheet.Name = "Dept Card Q" & QCount ActiveSheet.Unprotect With ActiveWorkbook Links = .LinkSources(xlExcelLinks) If Not IsEmpty(Links) Then For i = 1 To UBound(Links) .BreakLink Links(i), xlLinkTypeExcelLinks Next i End If End With ActiveSheet.Protect Workbooks(Qbook).Close savechanges:=False Loop End If End If etc etc etc "Mike H" wrote: Hi, Then you are going to have to clarify what you mean by It just doesn't work in this loop... The code I posted works, if your doesnt then something in your code may be changing the value of qvalue so that it and Qcount can neve be equal. Mike "ewan7279" wrote: Hi Mike, Sorry, I should have included that I have already declared qvalue as an integer variable and it still does not work. The inputbox value (qvalue) works in an if statement before the loop, and there is a validation also to check the value entered does not exceed 4, with a msgbox resulting if it does. It just doesn't work in this loop... Any ideas? Thanks. "Mike H" wrote: Hi, Your inputbox is returning a text string so qvalue will never equal qcoount hence the endless loop. Try this:- Sub sonic() Dim Message, Title, Default Dim qvalue As Integer Message = "Please enter the number of the current quarter (1 to 4)" Title = "Enter Quarter" Default = 1 qvalue = InputBox(Message, Title, Default) Do Until qcount = qvalue qcount = qcount + 1 'my code here Loop MsgBox qcount End Sub Mike "ewan7279" wrote: Hi all, I cannot work out why this isn't working. The user inputs a number from 1 to 4 into an inputbox, and I want the macro to loop for this number of times (if 1 is entered, an if statement avoids this loop). My code is as below, but the loop just keeps on going for any number entered... Dim Message, Title, Default Message = "Please enter the number of the current quarter (1 to 4)" Title = "Enter Quarter" Default = "1" qvalue = InputBox(Message, Title, Default) Do Until QCount = qvalue QCount = QCount + 1 'my code here Loop Any ideas? Thanks, Ewan. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Loop & Count | Excel Programming | |||
| Loop & Count | Excel Programming | |||
| loop count | Excel Discussion (Misc queries) | |||
| inputbox loop | Excel Programming | |||
Linear Mode
