For Control Variable in use

G

gmazza76

Well-known Member
Joined
Mar 19, 2011
Messages
771
Office Version
  1. 365
Platform
  1. Windows
I have fixed 1 problem to face another.
Unfortunatley I am stuck and unaware of which way to go. I have highlighted the place it debugs below and the error is "For Control Variable in use" Is there anyway i can get around this. Also when i add the "next's" below the fault changes to "Block If without End If" Does this mean i have to have end if's to match If for each part of the VB. ie before gotcha i have 8 "If's" Do i need 8 "End If's" before it re loops?


Rich (BB code): 
'Open file
Dim gotcha As Boolean
Workbooks.Open Filename:="C:\Users\MAZZA\Documents\Gavin\Car Park\FYvData.xls" 'Home
'Workbooks.Open Filename:="\\W2K6082\COMMON\SHARED\Gavin Mazza\Car Park\FYVData.xls"
Dim myBook As Workbook
 
On Error Resume Next
Set myBook = Application.Workbooks("FYVData.xls")
On Error GoTo 0
 
Dim iRow As Long
Dim ws As Worksheet
Dim lRow As Integer
Set ws = Worksheets("Sheet1")
    If UserForm2.ComboBox1 = "Laura Haynes" Then
        Pword = InputBox("Please enter password")
        If Pword = "ticket" Then
            ActiveWorkbook.Sheets("Laura Haynes").Activate
        Else
            MsgBox "Please contact your administrator for a password"
            Workbooks("FYVData.xls").Close True
            Exit Sub
        End If
    End If
 
Unload Me
 
    lRow = Range("C" & Rows.Count).End(xlUp).Row
    For Each cell In Range("C1:C" & lRow)
        If cell.Value = ComboBox2.Value Then
            For i = 3 To lRow
                If Cells(i, 1).Value = "" Then Exit For
                If Cells(i, 1).Value <> "" And Cells(i, 14).Value = "" Then
                    gotcha = True
                    UserForm1.Show False
                    UserForm1.txtdate.Value = Cells(i, 1)
                    UserForm1.TextBox1.Value = Cells(i, 3)
                    UserForm1.TextBox13.Value = Cells(i, 2)
                    UserForm1.TextBox2.Value = Cells(i, 4)
                    UserForm1.TextBox3.Value = Cells(i, 7)
                    UserForm1.TextBox6.Value = Cells(i, 5)
                    UserForm1.TextBox5.Value = Cells(i, 6)
                    UserForm1.TextBox4.Value = Cells(i, 8)
                    UserForm1.TextBox7.Value = Cells(i, 9)
                    UserForm1.TextBox8.Value = Cells(i, 10)
                    UserForm1.TextBox9.Value = Cells(i, 13)
                    UserForm1.TextBox14.Value = Cells(i, 11)
                    UserForm1.TextBox15.Value = Cells(i, 12)
                    UserForm1.TextBox11.Value = Cells(i, 4)
 
                    With UserForm1.CBoxAdd
                         .AddItem "Christopher Nicholls"
                         .AddItem "Craig Keevney"
                         .AddItem "Daniel Littler"
                         .AddItem "Dean Morrison"
                         .AddItem "Ian Travis"
                         .AddItem "Ian Wilshaw"
                         .AddItem "Jennifer Cheetham-Shaw"
                         .AddItem "Katrina Whiting"
                         .AddItem "Lindsey Dodds"
                         .AddItem "Martyn Bryan"
                         .AddItem "Sonia Jennings"
                    End With
 
                    With UserForm1.txtdate
                        .Enabled = False
                    End With
                End If
            Next i
 
            If gotcha = False Then
                MsgBox "Nothing to Update"
                ActiveWindow.Close (True)
            End If
        End If
    Next 'Removed ' before next and added next below in RED
        gotcha = False
 
    'If Louise Makin - callcentre enquiry
        If UserForm2.ComboBox1 = "Sheet1" Then
 
            Pword = InputBox("Please enter password")
            If Pword = "judy" Then
                ActiveWorkbook.Sheets("Louise Makin").Activate
            Else
                MsgBox "Please contact your administrator for a password"
                Workbooks("FYVData.xls").Close True
                Exit Sub
            End If
 
            Unload Me
 
 
    lRow = Range("C" & Rows.Count).End(xlUp).Row
    For Each cell In Range("C1:C" & lRow)
        If cell.Value = ComboBox2.Value Then
            For i = 3 To lRow
                If Cells(i, 1).Value = "" Then Exit For
                If Cells(i, 1).Value <> "" And Cells(i, 14).Value = "" Then
                    gotcha = True
                    UserForm1.Show False
                    UserForm1.txtdate.Value = Cells(i, 1)
                    UserForm1.TextBox1.Value = Cells(i, 3)
                    UserForm1.TextBox13.Value = Cells(i, 2)
                    UserForm1.TextBox2.Value = Cells(i, 4)
                    UserForm1.TextBox3.Value = Cells(i, 7)
                    UserForm1.TextBox6.Value = Cells(i, 5)
                    UserForm1.TextBox5.Value = Cells(i, 6)
                    UserForm1.TextBox4.Value = Cells(i, 8)
                    UserForm1.TextBox7.Value = Cells(i, 9)
                    UserForm1.TextBox8.Value = Cells(i, 10)
                    UserForm1.TextBox9.Value = Cells(i, 13)
                    UserForm1.TextBox15.Value = Cells(i, 12)
                    UserForm1.TextBox14.Value = Cells(i, 11)
                    UserForm1.TextBox11.Value = Cells(i, 4)
 
                    With UserForm1.CBoxAdd
                        .AddItem "Anthony Fox"
                        .AddItem "Ben Buckley"
                        .AddItem "Joanna Hanson"
                        .AddItem "Keith Drummond"
                        .AddItem "Linda Mason"
                        .AddItem "Lisa Bagulay"
                        .AddItem "Marie Lyons"
                        .AddItem "Norman Young"
                        .AddItem "Philip Stanistreet"
                        .AddItem "Rebecca Lees"
                        .AddItem "Samantha Ralph"
                        .AddItem "Tom Benson"
                        .AddItem "Tracy Carroll"
                    End With
 
                    With UserForm1.txtdate
                        .Enabled = False
                    End With
                End If
            Next i
 
            If gotcha = False Then
                MsgBox "Nothing to Update"
                ActiveWindow.Close (True)
            End If
        End If
 
        gotcha = False
 Next
    'If Lorraine Tinnion - callcentre enquiry
        If UserForm2.ComboBox1 = "Sheet1" Then
            Pword = InputBox("Please enter password")
            If Pword = "sharples" Then
                ActiveWorkbook.Sheets("Lorraine Tinnion").Activate
            Else
                MsgBox "Please contact your administrator for a password"
                Workbooks("FYVData.xls").Close True
                Exit Sub
            End If
 
            Unload Me
 
    lRow = Range("C" & Rows.Count).End(xlUp).Row
    For Each cell In Range("C1:C" & lRow)
        If cell.Value = ComboBox2.Value Then
            For i = 3 To lRow
                If Cells(i, 1).Value = "" Then Exit For
                If Cells(i, 1).Value <> "" And Cells(i, 14).Value = "" Then
                    gotcha = True
                    UserForm1.Show False
                    UserForm1.txtdate.Value = Cells(i, 1)
                    UserForm1.TextBox1.Value = Cells(i, 3)
                    UserForm1.TextBox13.Value = Cells(i, 2)
                    UserForm1.TextBox2.Value = Cells(i, 4)
                    UserForm1.TextBox3.Value = Cells(i, 7)
                    UserForm1.TextBox6.Value = Cells(i, 5)
                    UserForm1.TextBox5.Value = Cells(i, 6)
                    UserForm1.TextBox4.Value = Cells(i, 8)
                    UserForm1.TextBox7.Value = Cells(i, 9)
                    UserForm1.TextBox8.Value = Cells(i, 10)
                    UserForm1.TextBox9.Value = Cells(i, 13)
                    UserForm1.TextBox14.Value = Cells(i, 11)
                    UserForm1.TextBox15.Value = Cells(i, 12)
                    UserForm1.TextBox11.Value = Cells(i, 4)
 
                    With UserForm1.CBoxAdd
                        .AddItem "Alan Cotton"
                        .AddItem "Alex McQuarrie"
                        .AddItem "David Bowler"
                        .AddItem "Emma Coleman"
                        .AddItem "James Anthony Wood"
                        .AddItem "John O'Doherty"
                        .AddItem "Kirsten Hough"
                        .AddItem "Kurt Melia"
                        .AddItem "Luke Cunningham"
                        .AddItem "Mohsin Inam"
                        .AddItem "Paul Lunn"
                        .AddItem "Rabiha Younis"
                        .AddItem "Sarah Lloyd"
                        .AddItem "Sarah Whitehead"
                    End With
 
                    With UserForm1.txtdate
                        .Enabled = False
                    End With
                End If
            Next i
 
            If gotcha = False Then
                MsgBox "Nothing to Update"
                ActiveWindow.Close (True)
            End If
        End If
    Next
 
End Sub
 
Last edited:

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
As you are looping anyway you do not really need to redifine Lrow etc although you are showing a next after the first next i you then say it has been moved further down the macro thus making the first for each cell still valid when you are saying for each cell again, hence the error, this may have been better written using select case combobox2.value
 
Upvote 0
Sorry about this i have been using a lot of help to try and make this work.

When you say "this may have been better written using select case combobox2.value " which is my manager name i do understand this point i have used combobox1 as the main point so i can create 3 seperate passwords so i dont have to create around 13 for each individual manager.

How can i remove the LRow and still get this to work.

Many Thanks
<!-- / message -->
 
Upvote 0
There's a lot of places in that code that could be causing that error and it's pretty hard to tell where it is exactly.

It could be a problem with a With End With, a If End If or one of the loops.

What are you trying to populate the textboxes with?

Do you actually need loops?
 
Upvote 0
This code will compile but I can't be sure it'll actually do what you want.
Rich (BB code): 
Option Explicit
Sub test()
'Open file
Dim gotcha As Boolean
Dim iRow As Long
Dim ws As Worksheet
Dim lRow As Integer
Dim myBook As Workbook
Dim Pword
Dim cell As Range
Dim I As Long
Set myBook = Workbooks.Open(Filename:="C:\Users\MAZZA\Documents\Gavin\Car Park\FYvData.xls")     'Home
    Workbooks.Open Filename:="\\W2K6082\COMMON\SHARED\Gavin Mazza\Car Park\FYVData.xls"

    Set ws = myBook.Worksheets("Sheet1")
    If UserForm2.ComboBox1 = "Laura Haynes" Then
        Pword = InputBox("Please enter password")
        If Pword = "ticket" Then
            ActiveWorkbook.Sheets("Laura Haynes").Activate
        Else
            MsgBox "Please contact your administrator for a password"
            myBook.Close True
            Exit Sub
        End If
    End If
    Unload Me

    lRow = Range("C" & Rows.Count).End(xlUp).Row
    For Each cell In Range("C1:C" & lRow)
        If cell.Value = ComboBox2.Value Then
            For I = 3 To lRow
                If Cells(I, 1).Value = "" Then Exit For
                If Cells(I, 1).Value <> "" And Cells(I, 14).Value = "" Then
                    gotcha = True
                    UserForm1.Show False
                    UserForm1.txtdate.Value = Cells(I, 1)
                    UserForm1.TextBox1.Value = Cells(I, 3)
                    UserForm1.TextBox13.Value = Cells(I, 2)
                    UserForm1.TextBox2.Value = Cells(I, 4)
                    UserForm1.TextBox3.Value = Cells(I, 7)
                    UserForm1.TextBox6.Value = Cells(I, 5)
                    UserForm1.TextBox5.Value = Cells(I, 6)
                    UserForm1.TextBox4.Value = Cells(I, 8)
                    UserForm1.TextBox7.Value = Cells(I, 9)
                    UserForm1.TextBox8.Value = Cells(I, 10)
                    UserForm1.TextBox9.Value = Cells(I, 13)
                    UserForm1.TextBox14.Value = Cells(I, 11)
                    UserForm1.TextBox15.Value = Cells(I, 12)
                    UserForm1.TextBox11.Value = Cells(I, 4)
                End If
            Next I
            UserForm1.CBoxAdd.List = Array("Christopher Nicholls", "Craig Keevney", "Daniel Littler", "Dean Morrison", _
                                           "Ian Travis", "Ian Wilshaw", "Jennifer Cheetham-Shaw", "Katrina Whiting", _
                                           "Lindsey Dodds", "Martyn Bryan", "Sonia Jennings")
            UserForm1.txtdate.Enabled = False
        End If

        If gotcha = False Then
            MsgBox "Nothing to Update"
            ActiveWindow.Close (True)
        End If
    Next cell
    'Removed ' before next and added next below in RED
    gotcha = False
    'If Louise Makin - callcentre enquiry
    If UserForm2.ComboBox1 = "Sheet1" Then
        Pword = InputBox("Please enter password")
        If Pword = "judy" Then
            ActiveWorkbook.Sheets("Louise Makin").Activate
        Else
            MsgBox "Please contact your administrator for a password"
            Workbooks("FYVData.xls").Close True
            Exit Sub
        End If
        Unload Me

        lRow = Range("C" & Rows.Count).End(xlUp).Row
        For Each cell In Range("C1:C" & lRow)
            If cell.Value = ComboBox2.Value Then
                For I = 3 To lRow
                    If Cells(I, 1).Value = "" Then Exit For
                    If Cells(I, 1).Value <> "" And Cells(I, 14).Value = "" Then
                        gotcha = True
                        UserForm1.Show False
                        UserForm1.txtdate.Value = Cells(I, 1)
                        UserForm1.TextBox1.Value = Cells(I, 3)
                        UserForm1.TextBox13.Value = Cells(I, 2)
                        UserForm1.TextBox2.Value = Cells(I, 4)
                        UserForm1.TextBox3.Value = Cells(I, 7)
                        UserForm1.TextBox6.Value = Cells(I, 5)
                        UserForm1.TextBox5.Value = Cells(I, 6)
                        UserForm1.TextBox4.Value = Cells(I, 8)
                        UserForm1.TextBox7.Value = Cells(I, 9)
                        UserForm1.TextBox8.Value = Cells(I, 10)
                        UserForm1.TextBox9.Value = Cells(I, 13)
                        UserForm1.TextBox15.Value = Cells(I, 12)
                        UserForm1.TextBox14.Value = Cells(I, 11)
                        UserForm1.TextBox11.Value = Cells(I, 4)
                        UserForm1.txtdate.Enabled = False
                    End If
                Next I
                UserForm1.CBoxAdd.List = Array("Anthony Fox", "Ben Buckley", "Joanna Hanson", "Keith Drummond", "Linda Mason", _
                                               "Lisa Bagulay", "Marie Lyons", "Norman Young", "Philip Stanistreet", "Rebecca Lees", _
                                               "Samantha Ralph", "Tom Benson", "Tracy Carroll")
                If gotcha = False Then
                    MsgBox "Nothing to Update"
                    ActiveWindow.Close (True)
                End If
            End If
            gotcha = False
        Next cell
    End If
    '    If Lorraine Tinnion - callcentre enquiry
    If UserForm2.ComboBox1 = "Sheet1" Then
        Pword = InputBox("Please enter password")
        If Pword = "sharples" Then
            ActiveWorkbook.Sheets("Lorraine Tinnion").Activate
        Else
            MsgBox "Please contact your administrator for a password"
            Workbooks("FYVData.xls").Close True
            Exit Sub
        End If
        Unload Me
        lRow = Range("C" & Rows.Count).End(xlUp).Row
        For Each cell In Range("C1:C" & lRow)
            If cell.Value = ComboBox2.Value Then
                For I = 3 To lRow
                    If Cells(I, 1).Value = "" Then Exit For
                    If Cells(I, 1).Value <> "" And Cells(I, 14).Value = "" Then
                        gotcha = True
                        UserForm1.Show False
                        UserForm1.txtdate.Value = Cells(I, 1)
                        UserForm1.TextBox1.Value = Cells(I, 3)
                        UserForm1.TextBox13.Value = Cells(I, 2)
                        UserForm1.TextBox2.Value = Cells(I, 4)
                        UserForm1.TextBox3.Value = Cells(I, 7)
                        UserForm1.TextBox6.Value = Cells(I, 5)
                        UserForm1.TextBox5.Value = Cells(I, 6)
                        UserForm1.TextBox4.Value = Cells(I, 8)
                        UserForm1.TextBox7.Value = Cells(I, 9)
                        UserForm1.TextBox8.Value = Cells(I, 10)
                        UserForm1.TextBox9.Value = Cells(I, 13)
                        UserForm1.TextBox14.Value = Cells(I, 11)
                        UserForm1.TextBox15.Value = Cells(I, 12)
                        UserForm1.TextBox11.Value = Cells(I, 4)
                        UserForm1.CBoxAdd = Array("Alan Cotton", "Alex McQuarrie", "David Bowler", "Emma Coleman", _
                                                  "James Anthony Wood", "John O'Doherty", "Kirsten Hough", "Kurt Melia", _
                                                  "Luke Cunningham", "Mohsin Inam", "Paul Lunn", "Rabiha Younis", _
                                                  "Sarah Lloyd", "Sarah Whitehead")

                        UserForm1.txtdate.Enabled = False
                    End If
                Next I
            End If

        Next cell
    End If
    If gotcha = False Then
        MsgBox "Nothing to Update"
        ActiveWindow.Close (True)
    End If

End Sub
I removed the withs because it didn't look like they were actually necessary.

The main change I made was moving some of the End Ifs and Nexts.

Still not sure you actually need all the loops though.:)
 
Upvote 0
Good Afternoon,

I have gone with your idea of using just combobox2 to search existing records. I have 2 questions if you have 5 minutes

1. Where i had a password setup before for combobox1 can i amend it to have 1 password for 6 managers another for 6 etc..
2. Deletion of line from "Sheet1" that has been uploaded into the userform

Everything else seems to work i just need to allow certain people access to certain managers info.

Rich (BB code): 
'Open file
Dim gotcha As Boolean
Workbooks.Open Filename:="C:\Users\MAZZA\Documents\Gavin\Car Park\FYvData.xls" 'Home
Dim myBook As Workbook
 
On Error Resume Next
Set myBook = Application.Workbooks("FYVData.xls")
On Error GoTo 0
 
Dim iRow As Long
Dim ws As Worksheet
Dim lRow As Integer
Set ws = Worksheets("Sheet1")
    If UserForm2.ComboBox2 = "Abdulaziz Abbas" ',"2nd Manager Name","3rd Manager Name" Then
        Pword = InputBox("Please enter password")
        If Pword = "ticket" Then
            ActiveWorkbook.Sheets("Sheet1").Activate
        Else
            MsgBox "Please contact your administrator for a password"
            Workbooks("FYVData.xls").Close True
            Exit Sub
'Then it needs to loop for another set of managers maximum of 2 other sets of managers 
        End If
    End If
 
Unload Me
 
    lRow = Range("C" & Rows.Count).End(xlUp).Row
    For Each cell In Range("C1:C" & lRow)
        If cell.Value = ComboBox2.Value Then
            For i = 3 To lRow
                If Cells(i, 1).Value = "" Then Exit For
                If Cells(i, 1).Value <> "" And Cells(i, 14).Value = "" Then
                    gotcha = True
                    UserForm1.Show False
                    UserForm1.txtdate.Value = Cells(i, 1)
                    UserForm1.TextBox1.Value = Cells(i, 3)
                    UserForm1.TextBox13.Value = Cells(i, 2)
                    UserForm1.TextBox2.Value = Cells(i, 4)
                    UserForm1.TextBox3.Value = Cells(i, 7)
                    UserForm1.TextBox6.Value = Cells(i, 5)
                    UserForm1.TextBox5.Value = Cells(i, 6)
                    UserForm1.TextBox4.Value = Cells(i, 8)
                    UserForm1.TextBox7.Value = Cells(i, 9)
                    UserForm1.TextBox8.Value = Cells(i, 10)
                    UserForm1.TextBox9.Value = Cells(i, 13)
                    UserForm1.TextBox14.Value = Cells(i, 11)
                    UserForm1.TextBox15.Value = Cells(i, 12)
                    UserForm1.TextBox11.Value = Cells(i, 4)
 
                    ActiveCell.EntireRow.Delete 'This should delete the line that has been populated into the form
 
                    Workbooks("FYVData.xls").Close SaveChanges:=True
 
                    With UserForm1.txtdate
                        .Enabled = False
                    End With
                End If
            Next i
 
            If gotcha = False Then
                MsgBox "Nothing to Update"
                ActiveWindow.Close (True)
            End If
        End If
    Next
 
End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Msg box for
Replies
1
Views
256
Micron
M
D
  • Question Question
Open 1 UserForm then open another UserForm
Replies
4
Views
417
dharv3y78
D
J
  • Question Question
search all worksheets based upon textbox value and add to listbox
Replies
7
Views
408
Jaye7
J
I
  • Question Question
Listbox columns dont alter when i set the width advice please
Replies
6
Views
119
Skyybot
Skyybot
E
  • Question Question
Search function not finding rows containing the matching value
Replies
1
Views
671
GlennUK
G

Forum statistics

Threads
1,224,609
Messages
6,179,879
Members
452,948
Latest member
Dupuhini

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