VBA loop not functioning - Please help!

D

DwinZly

Board Regular
Joined
Oct 26, 2009
Messages
56
Hi,

I have created a data entry macro that prompts the user for information and then enters it in the correct cell of the excel sheet. The user will be entering multiple lines of information so I need the macro called "newentry()" to repeat itself indefinitely, at least until the user clicks "Cancel". I tried adding a loop around the calling of the sub-macros but it won't repeat itself from the beginning once it calls the last macro. Any help is greatly appreciated. Thanks

Code: 
Do
Sub newentry()
activate
EnterSO
Enterclientname
Entersizequal
Entertreat
EnterBin
EnterMachine
Enterdate
EnterResult1
EnterResult2
EnterResult3
protect
Loop Until IsEmpty(ActiveSheet.Cells(1, 1))
 
End Sub
 
Sub activate()
Sheets("2012 Extraction Results").Select
 
    ActiveSheet.Unprotect
 
End Sub
 
Sub EnterSO()
'
' EnterSO Macro
LastRowColB = Range("B65536").End(xlUp).Row
 
LastSOentry = ActiveSheet.Cells(LastRowColB, 2).Value
SOInput = InputBox(Prompt:="Type the last 5 digits of the SO # or scan the barcode now.", _
          Title:="SO # Entry", Default:=LastSOentry)
On Error GoTo protected:
          socut = CLng(Right(SOInput, 5))
 
        If SOInput = "Enter SO # here" Or _
         SOInput = 0 Or _
        SOInput = vbNullString Then
protected:
protect
        End
        Else
        exist = Application.Match(socut, ActiveSheet.Range("B:B"), 0)
 
        If IsError(exist) Then
 
         LastRowColB = Range("B65536").End(xlUp).Row
         ActiveSheet.Cells(LastRowColB + 1, 2).Value = socut
        Else
        customer = Application.VLookup(socut, ActiveSheet.Range("B:J"), 2, False)
        sizequal = Application.VLookup(socut, ActiveSheet.Range("B:J"), 3, False)
        treatment = Application.VLookup(socut, ActiveSheet.Range("B:J"), 4, False)
 
 
        doesexist = MsgBox("Prior record of SO # " & socut & " found. Please confirm the details:" & vbNewLine & "Customer:       " & customer & vbNewLine & "Size/Qualtiy:    " & sizequal & vbNewLine & "Treatment:      " & treatment & vbNewLine & "Is this correct?", vbYesNoCancel, "Record of SO # " & socut & " found!")
 
If doesexist = vbYes Then
LastRowColB = Range("B65536").End(xlUp).Row
         ActiveSheet.Cells(LastRowColB + 1, 2).Value = socut
         ActiveSheet.Cells(LastRowColB + 1, 3).Value = customer
         ActiveSheet.Cells(LastRowColB + 1, 4).Value = sizequal
         ActiveSheet.Cells(LastRowColB + 1, 5).Value = treatment
Foundentry
ElseIf doesexist = vbNo Then
LastRowColB = Range("B65536").End(xlUp).Row
         ActiveSheet.Cells(LastRowColB + 1, 2).Value = socut
ElseIf doesexist = vbCancel Then
protect
End
End If
 
 End If
 
   End If
End Sub
 
Sub Enterclientname()
LastRowColB = Range("B65536").End(xlUp).Row
      SOInput = ActiveSheet.Cells(LastRowColB, 2).Value
 
NameInput = InputBox(Prompt:="Type the customer name.", _
          Title:="Customer Name for SO# " & SOInput, Default:="Enter customer name here")
 
 
        If NameInput = "Enter customer name here" Or _
        NameInput = vbNullString Then
 
    LastRowColB = Range("B65536").End(xlUp).Row
      Range("A" & LastRowColB & ":J" & LastRowColB).ClearContents
      protect
          End
        Else
        LastRowColB = Range("B65536").End(xlUp).Row
ActiveSheet.Cells(LastRowColB, 3).Value = NameInput
        End If
 
 
End Sub
 
Sub Entertreat()
answer = MsgBox("Bopsil treated?" & vbNewLine & "(clicking No selects paraffin/silicone)", vbYesNoCancel + vbDefaultButton4, "Treatment Entry")
If answer = vbYes Then
treatment = "Bopsil"
ElseIf answer = vbNo Then
treatment = "Paraffin/silicone"
ElseIf answer = vbCancel Then
LastRowColB = Range("B65536").End(xlUp).Row
      Range("A" & LastRowColB & ":J" & LastRowColB).ClearContents
      protect
          End
End If
 
LastRowColB = Range("B65536").End(xlUp).Row
ActiveSheet.Cells(LastRowColB, 5).Value = treatment
 
End Sub
 
Sub Entersizequal()
SizequalInput = InputBox(Prompt:="Type the size/quality (45Nat etc.).", _
          Title:="Size/quality Entry for SO# " & SOInput, Default:="Enter size/quality here")
 
        If SizequalInput = "Enter size/quality here" Or _
           SizequalInput = vbNullString Then
    LastRowColB = Range("B65536").End(xlUp).Row
      Range("A" & LastRowColB & ":J" & LastRowColB).ClearContents
      protect
          End
        Else
        LastRowColB = Range("B65536").End(xlUp).Row
ActiveSheet.Cells(LastRowColB, 4).Value = SizequalInput
        End If
 
 
End Sub
 
Sub EnterBin()
LastRowColB = Range("B65536").End(xlUp).Row
 
lastbin = ActiveSheet.Cells(LastRowColB - 1, 6).Value + 1
BinInput = InputBox(Prompt:="Scan or type the bin #.", _
          Title:="Bin # Entry for SO# " & SOInput, Default:=lastbin)
 
        If BinInput = "Enter bin # here" Or _
           BinInput = vbNullString Then
  LastRowColB = Range("B65536").End(xlUp).Row
      Range("A" & LastRowColB & ":J" & LastRowColB).ClearContents
      protect
          End
        Else
        LastRowColB = Range("B65536").End(xlUp).Row
ActiveSheet.Cells(LastRowColB, 6).Value = BinInput
        End If
 
End Sub
 
Sub EnterMachine()
LastRowColB = Range("B65536").End(xlUp).Row
 
lastmachine = ActiveSheet.Cells(LastRowColB - 1, 7).Value
If lastmachine = 1 Then
lastmachineedit = lastmachine + 1
Else
lastmachineedit = lastmachine - 1
End If
 
MachineInput = InputBox(Prompt:="Scan or type the machine #.", _
          Title:="Machine # Entry for SO# " & SOInput, Default:=lastmachineedit)
 
        If MachineInput = "Enter machine # here" Or _
           MachineInput = vbNullString Then
     LastRowColB = Range("B65536").End(xlUp).Row
      Range("A" & LastRowColB & ":J" & LastRowColB).ClearContents
      protect
          End
        Else
        LastRowColB = Range("B65536").End(xlUp).Row
ActiveSheet.Cells(LastRowColB, 7).Value = MachineInput
        End If
 
End Sub
 
Sub Enterdate()
LastRowColB = Range("B65536").End(xlUp).Row
 
lastdate = ActiveSheet.Cells(LastRowColB - 1, 1).Value
DateInput = InputBox(Prompt:="Type the treatment date.", _
          Title:="Treatment Date Entry for SO# " & SOInput, Default:=lastdate)
 
        If DateInput = "Enter treatment date here" Or _
           DateInput = vbNullString Then
     LastRowColB = Range("B65536").End(xlUp).Row
      Range("A" & LastRowColB & ":J" & LastRowColB).ClearContents
      protect
          End
        Else
        LastRowColB = Range("B65536").End(xlUp).Row
ActiveSheet.Cells(LastRowColB, 1).Value = DateInput
        End If
 
End Sub
 
Sub EnterResult1()
Result1Input = InputBox(Prompt:="Enter result 1 manually or start the extraction on the Dillon extraction force meter.", _
          Title:="Result 1 Entry for SO# " & SOInput, Default:="Enter result 1 here")
 
        If Result1Input = "Enter result 1 here" Or _
           Result1Input = vbNullString Then
           Exit Sub
        Else
        LastRowColB = Range("B65536").End(xlUp).Row
ActiveSheet.Cells(LastRowColB, 8).Value = Result1Input
        End If
 
End Sub
 
Sub EnterResult2()
Result2Input = InputBox(Prompt:="Enter result 2 manually or start the extraction on the Dillon extraction force meter.", _
          Title:="Result 2 Entry for SO# " & SOInput, Default:="Enter result 2 here")
 
        If Result2Input = "Enter result 2 here" Or _
           Result2Input = vbNullString Then
           Exit Sub
        Else
        LastRowColB = Range("B65536").End(xlUp).Row
ActiveSheet.Cells(LastRowColB, 9).Value = Result2Input
        End If
 
End Sub
 
Sub EnterResult3()
Result3Input = InputBox(Prompt:="Enter result 3 manually or start the extraction on the Dillon extraction force meter.", _
          Title:="Result 3 Entry for SO# " & SOInput, Default:="Enter result 3 here")
 
        If Result3Input = "Enter result 3 here" Or _
           Result3Input = vbNullString Then
ActiveSheet.protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowSorting:=True, AllowFiltering:=True
           Exit Sub
        Else
        LastRowColB = Range("B65536").End(xlUp).Row
ActiveSheet.Cells(LastRowColB, 10).Value = Result3Input
        End If
            ActiveSheet.protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowSorting:=True, AllowFiltering:=True
End Sub
 
Sub protect()
    ActiveSheet.protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowSorting:=True, AllowFiltering:=True
 
End Sub
 
 
Sub Lastrow()
'
' Lastrow Macro
LastRowColB = Range("B65536").End(xlUp).Row
ActiveSheet.Cells(LastRowColB + 1, 1).Value = LastRowColB
ActiveSheet.Cells(LastRowColB + 1, 2).Value = [G1].Value
'
End Sub
 
Sub Foundentry()
EnterBin
EnterMachine
Enterdate
EnterResult1
EnterResult2
EnterResult3
End
End Sub
 
Last edited:

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
Hi DwinZly,

I have few comments:

1. Haven’t seen the loop that calls newentry.

2. You have two procedures named activate and protect. These two names are existing Excel members, so rename activate as UnprotectSheet and protect as ProtectSheet.

3. Avoid using End in the middle of the code as this is not a good programming practice.

4. I recommend converting all data entry procedures to functions, so you can examine the return value and decide on the appropriate action.

Here is a sample function code,

Code: 
[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Function EnterSO() As Boolean       [COLOR="green"]'* Change to function so it returns a value[/COLOR]

    Dim SOInput As String           [COLOR="green"]'* Declare variables for easy tracking[/COLOR]
    Dim LastRowColB As Long
    Dim LastSOentry As Long

    EnterSO = True                  [COLOR="green"]'* Set the return value to true in the beginning[/COLOR]
[COLOR="green"]'*
'*  Code
'*[/COLOR]
    SOInput = InputBox(Prompt:="Type the last 5 digits of the SO # or scan the barcode now.", _
          Title:="SO # Entry", Default:=LastSOentry)
    If SOInput = "" Or SOInput = "Enter SO # here" Or _
        SOInput = 0 Then
        EnterSO = False
        Exit Function
    End If
[COLOR="green"]'*
'*  Code
'*[/COLOR]
    If ThereIsProblem Then
        EnterSO = False
        Exit Function
    End If
[COLOR="green"]'*
'*  Code
'*[/COLOR]
End Function    [COLOR="Green"]'* Data entered correctly with EnterSO = True[/COLOR]
[/COLOR][/SIZE][/FONT]
As you can see, if there is a problem the function will return False and if everything is fine then it will return True.This way, you can test the return value of each function in NewEntry and exit the loop if the value is False,
Code: 
[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Sub NewEntry()

    UnprotectSheet          [COLOR="Green"]'* Leave as Sub[/COLOR]
    Do
        If Not EnterSO Then Exit Do
        If Not Enterclientname Then Exit Do
        If Not Entersizequal Then Exit Do
        If Not Entertreat Then Exit Do
        If Not EnterBin Then Exit Do
        If Not EnterMachine Then Exit Do
        If Not Enterdate Then Exit Do
        If Not EnterResult1 Then Exit Do
        If Not EnterResult2 Then Exit Do
        If Not EnterResult3 Then Exit Do
    Loop
    ProtectSheet            [COLOR="green"]'* Leave as Sub[/COLOR]

End Sub[/COLOR][/SIZE][/FONT]
 
Upvote 0
Thanks Mohammed. I will try the conversion route. newentry is a macro that is called from the user which is nested in a button. Also, is "Thereisproblem" really a member? Thanks again.

Mohammad Basem said:
Hi DwinZly,

I have few comments:

1. Haven’t seen the loop that calls newentry.

2. You have two procedures named activate and protect. These two names are existing Excel members, so rename activate as UnprotectSheet and protect as ProtectSheet.

3. Avoid using End in the middle of the code as this is not a good programming practice.

4. I recommend converting all data entry procedures to functions, so you can examine the return value and decide on the appropriate action.

Here is a sample function code,

Code: 
[FONT=Consolas][SIZE=2][COLOR=navy]Function EnterSO() As Boolean       [COLOR=green]'* Change to function so it returns a value[/COLOR][/COLOR][/SIZE][/FONT][COLOR=navy]
 
[SIZE=2][FONT=Consolas]   Dim SOInput As String           [COLOR=green]'* Declare variables for easy tracking[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas]   Dim LastRowColB As Long[/FONT][/SIZE]
[SIZE=2][FONT=Consolas]   Dim LastSOentry As Long[/FONT][/SIZE]
 
[SIZE=2][FONT=Consolas]   EnterSO = True                  [COLOR=green]'* Set the return value to true in the beginning[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=green]'*[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=green]'*  Code[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=green]'*[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas]   SOInput = InputBox(Prompt:="Type the last 5 digits of the SO # or scan the barcode now.", _[/FONT][/SIZE]
[SIZE=2][FONT=Consolas]         Title:="SO # Entry", Default:=LastSOentry)[/FONT][/SIZE]
[SIZE=2][FONT=Consolas]   If SOInput = "" Or SOInput = "Enter SO # here" Or _[/FONT][/SIZE]
[SIZE=2][FONT=Consolas]       SOInput = 0 Then[/FONT][/SIZE]
[SIZE=2][FONT=Consolas]       EnterSO = False[/FONT][/SIZE]
[SIZE=2][FONT=Consolas]       Exit Function[/FONT][/SIZE]
[SIZE=2][FONT=Consolas]   End If[/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=green]'*[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=green]'*  Code[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=green]'*[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas]   If ThereIsProblem Then[/FONT][/SIZE]
[SIZE=2][FONT=Consolas]       EnterSO = False[/FONT][/SIZE]
[SIZE=2][FONT=Consolas]       Exit Function[/FONT][/SIZE]
[SIZE=2][FONT=Consolas]   End If[/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=green]'*[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=green]'*  Code[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=green]'*[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas]End Function    [COLOR=green]'* Data entered correctly with EnterSO = True[/COLOR][/FONT][/SIZE]
[/COLOR]
As you can see, if there is a problem the function will return False and if everything is fine then it will return True.This way, you can test the return value of each function in NewEntry and exit the loop if the value is False,
Code: 
[FONT=Consolas][SIZE=2][COLOR=navy]Sub NewEntry()[/COLOR][/SIZE][/FONT]
 
[SIZE=2][FONT=Consolas][COLOR=navy]   UnprotectSheet          [COLOR=green]'* Leave as Sub[/COLOR][/COLOR][/FONT][/SIZE][COLOR=navy]
[SIZE=2][FONT=Consolas]   Do[/FONT][/SIZE]
[SIZE=2][FONT=Consolas]       If Not EnterSO Then Exit Do[/FONT][/SIZE]
[SIZE=2][FONT=Consolas]       If Not Enterclientname Then Exit Do[/FONT][/SIZE]
[SIZE=2][FONT=Consolas]       If Not Entersizequal Then Exit Do[/FONT][/SIZE]
[SIZE=2][FONT=Consolas]       If Not Entertreat Then Exit Do[/FONT][/SIZE]
[SIZE=2][FONT=Consolas]       If Not EnterBin Then Exit Do[/FONT][/SIZE]
[SIZE=2][FONT=Consolas]       If Not EnterMachine Then Exit Do[/FONT][/SIZE]
[SIZE=2][FONT=Consolas]       If Not Enterdate Then Exit Do[/FONT][/SIZE]
[SIZE=2][FONT=Consolas]       If Not EnterResult1 Then Exit Do[/FONT][/SIZE]
[SIZE=2][FONT=Consolas]       If Not EnterResult2 Then Exit Do[/FONT][/SIZE]
[SIZE=2][FONT=Consolas]       If Not EnterResult3 Then Exit Do[/FONT][/SIZE]
[SIZE=2][FONT=Consolas]   Loop[/FONT][/SIZE]
[SIZE=2][FONT=Consolas]   ProtectSheet            [COLOR=green]'* Leave as Sub[/COLOR][/FONT][/SIZE]
 
[SIZE=2][FONT=Consolas]End Sub[/FONT][/SIZE][/COLOR]
Click to expand...
 
Upvote 0
ThereIsProblem is not a member, it is mine :)

It means you can do all the verifications you need, and once you find a problem then use its If...Then...End If block.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
 
Upvote 0
Will changing my subs to functions take away my ability to alter the contents of my sheet? ie will I be able to have the function insert the obtained data into a cell? Thanks
Mohammad Basem said:
ThereIsProblem is not a member, it is mine :)

It means you can do all the verifications you need, and once you find a problem then use its If...Then...End If block.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
Click to expand...
 
Upvote 0
Yes, you can write to the sheet from within a function.

You don't need to change your code, just insert/change the part that returns value whether True or False instead of "protect" and "End".

The return values of the functions are meant for controlling the flow of the program, whether to continue or not.
 
Upvote 0
Thanks very much! This suggestion worked like a charm. One more question though, considering I am new to FUNCTIONS....in a case similar to how I called Foundentry() in the middle of EnterSO() to bypass several of the macros, is there a way to call something from within EnterSO() that would allow me to exit function and then skip several functions in the newentry() loop and pick-up again right before the EnterBin() function. In other words, I would like to be able to "conditionally navigate". Thanks again.

Mohammad Basem said:
Yes, you can write to the sheet from within a function.

You don't need to change your code, just insert/change the part that returns value whether True or False instead of "protect" and "End".

The return values of the functions are meant for controlling the flow of the program, whether to continue or not.
Click to expand...
 
Upvote 0
If I well understood, you want EnterSO() to return a value that controls which function to execute next.

Actually you have triggered one of the mysteries that I could not understand. You are calling Foundentry() from within EnterSO(), which will call several functions like EnterBin() then you call EnterBin() again from newentry().

In any way, as long as you are returning a value from a function, you can decide on how to control the program’s flow. The return value is not necessarily be a True or False. It could be a number, text or object.

For example in newentry(), instead of testing the returned value and exiting the loop, you can add condition that decides whether to call or skip certain function or procedure.

An example:

Code: 
[COLOR=navy][SIZE=2][FONT=Consolas]Sub newentry()[/FONT][/SIZE][/COLOR]
[SIZE=2][FONT=Consolas][COLOR=navy].[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy].[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy].[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy]If EnterSO Then[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy]   If Not Enterclientname Then Exit Do[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy]End If[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy].[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy].[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy].[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy]End Sub[/COLOR][/FONT][/SIZE]

Which means Enterclientname() will not run if the return value of EnterSO() is False. In this case the return value of EnterSO will not break the loop; it is rather to control the flow.

Another example,

Code: 
[COLOR=navy][SIZE=2][FONT=Consolas]Sub newentry()[/FONT][/SIZE][/COLOR]
[SIZE=2][FONT=Consolas][COLOR=navy].[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy].[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy].[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy]If EnterSO = -1 Then Exit Do[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy]If EnterSO = 1 Then[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy]   If Not Enterclientname Then Exit Do[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy]End If[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy]If EnterSO = 2 Then[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy]   If Not Entersizequal Then Exit Do[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy]End If[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy].[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy].[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy].[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy]End Sub[/COLOR][/FONT][/SIZE]

In this case the return value of EnterSO is numeric, which gives wider control.

Hope I have clarified something.
 
Last edited:
Upvote 0
Correction to the 2nd example

Code: 
[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Sub newentry()
.
.
.
ControlValue = EnterSO
If ControlValue = -1 Then Exit Do
If ControlValue = 1 Then
   If Not Enterclientname Then Exit Do
End If
If ControlValue = 2 Then
   If Not Entersizequal Then Exit Do
End If
.
.
.
End Sub[/COLOR][/SIZE][/FONT]
 
Upvote 0
Then would I have to change the designator from boolean to long or something like that?

Can a function recall a set value from a function inside of it? ie if I called function2 from within function1, set a vlaue within function2 and then exited, could I recall that value from function1?
Mohammad Basem said:
Correction to the 2nd example

Code: 
[FONT=Consolas][SIZE=2][COLOR=navy]Sub newentry()[/COLOR][/SIZE][/FONT]
[SIZE=2][FONT=Consolas][COLOR=navy].[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy].[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy].[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy]ControlValue = EnterSO[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy]If ControlValue = -1 Then Exit Do[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy]If ControlValue = 1 Then[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy]  If Not Enterclientname Then Exit Do[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy]End If[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy]If ControlValue = 2 Then[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy]  If Not Entersizequal Then Exit Do[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy]End If[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy].[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy].[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy].[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=navy]End Sub[/COLOR][/FONT][/SIZE]
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Pasting Value with VBA - Sorta Looping based on a multiple of 30
Replies
2
Views
190
nmbc99
N
A
  • Solved
Using VBA to loop through multiple Word Templates and fill them in with Excel Data
2
Replies
14
Views
313
John_w
J
N
  • Solved
VBA - Increment range and loop until blank Cell
Replies
3
Views
190
dreid1011
dreid1011
M
  • Solved
Help to exit VBA upon cancel on input box
Replies
3
Views
138
Domenic
D
sparky2205
  • Solved
#BLOCKED! error
2
Replies
10
Views
479
sparky2205
sparky2205

Forum statistics

Threads
1,216,533
Messages
6,131,216
Members
449,636
Latest member
ajdebm

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top