search and Load a Userform issue

G

gmazza76

Well-known Member
Joined
Mar 19, 2011
Messages
774
Office Version
  1. 365
Platform
  1. Windows
Good Morning,

The code below loads the data into my userform with no issue, but I need to load the userform and have the combobox1 options load also straight away instead of basing the combobox1 data on a change of the userform once its loaded.

I am confused on how to get this to work?
Should this be based where it is or adjusted in the actual userform once its loaded and if so where would I put the code concerning "Combobox1"

thanks
Gavin

Code: 
Dim myBook As Workbook
Dim iRow As Long
Dim FYV_Sht As Worksheet
Dim lRow As Integer
Dim I As Long
Dim gotcha As Boolean
Dim cCell As Variant
Dim MyEnquiryNo As String
Dim MyFind As Object
           
Application.ScreenUpdating = False
       
        MyEnquiryNo = Sheets("Today").Range("C17").Value
        Sheet2.Visible = True


    If MyEnquiryNo = "" Then
        MsgBox "Please enter Reference number to search"
            Sheets("Today").Range("C17").Select
    Else
        Set FYV_Sht = ActiveWorkbook.Sheets("Import Open")
            FYV_Sht.Select
        
        Set MyFind = FYV_Sht.Columns(1).Find(What:=MyEnquiryNo, lookat:=xlWhole, SearchDirection:=xlNext)
        If Not MyFind Is Nothing Then
            iRow = MyFind.Row
            Worked.Show False
            Worked.txtdate.Value = Cells(iRow, 1)
            Worked.txtdate.Locked = True
            Worked.TextBox3.Value = Cells(iRow, 2)
            Worked.ETType.Value = Cells(iRow, 7)
            Worked.ETType.Locked = True
            Worked.Rdate.Value = Cells(iRow, 3)
            Worked.Rdate.Locked = True
            Worked.TextBox4.Value = Cells(iRow, 8)
            Worked.TextBox5.Value = Cells(iRow, 9)
            Worked.TextBox6.Value = Cells(iRow, 10)
            Worked.TextBox7.Value = Cells(iRow, 13)
            Worked.TextBox2.Value = Cells(iRow, 14)
            Sheet2.Visible = xlVeryHidden
            Sheet1.Select
        Else
            MsgBox "Unable to Find " & MyEnquiryNo & ". Please try another Complaint/Enquiry.", vbOKOnly, "Manager Not Found"
            Sheet2.Visible = xlVeryHidden
                Sheets("Today").Select
                Range("C17").Select
        End If
        
    If ETType = "Complaint" Then
        With ComboBox1
            .AddItem "Complaint Pended - Awaiting Customer Info"
            .AddItem "Complaint Pended - Awaiting Escalation work"
            .AddItem "Complaint Pended - Awaiting Fault"
            .AddItem "Complaint Pended - Awaiting Site Visit"
            .AddItem "Complaint Pended - No Contact Process Initiated"
            .AddItem "Complaint Pended - To work"
            .AddItem "Complaint Closed - Assigned to Hit Team"
            .AddItem "Complaint Closed - Customer Satisfied"
            .AddItem "Complaint Closed - DEADLOCK"
            .AddItem "Complaint Closed - No Contact Process Followed"
            .AddItem "Complaint Closed - NFACC"
            End With
    If ETType = "Enquiry" Then
         With ComboBox1
            .AddItem "Enquiry Pended - Awaiting Customer Info"
            .AddItem "Enquiry Pended - Awaiting Escalation work"
            .AddItem "Enquiry Pended - Awaiting Fault"
            .AddItem "Enquiry Pended - Awaiting Site Visit"
            .AddItem "Enquiry Pended - No Contact Process Initiated"
            .AddItem "Enquiry Pended - To work"
            .AddItem "Enquiry Closed - Assigned to Hit Team"
            .AddItem "Enquiry Closed - Customer Satisfied"
            .AddItem "Enquiry Closed - DEADLOCK"
            .AddItem "Enquiry Closed - No Contact Process Followed"
            .AddItem "Enquiry Closed - NFACC"
        End With
    End If
    End If
End If
End Sub
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
To populate Cmbobox1 when the userForm loads, put the code below in the UserForm_Initialize procedure. I don't know what default options you want. This uses the "Comlaint" combobox1 options.

Code: 
[color=darkblue]Private[/color] [color=darkblue]Sub[/color] UserForm_Initialize()
    [color=darkblue]With[/color] ComboBox1
        .AddItem "Complaint Pended - Awaiting Customer Info"
        .AddItem "Complaint Pended - Awaiting Escalation work"
        .AddItem "Complaint Pended - Awaiting Fault"
        .AddItem "Complaint Pended - Awaiting Site Visit"
        .AddItem "Complaint Pended - No Contact Process Initiated"
        .AddItem "Complaint Pended - To work"
        .AddItem "Complaint Closed - Assigned to Hit Team"
        .AddItem "Complaint Closed - Customer Satisfied"
        .AddItem "Complaint Closed - DEADLOCK"
        .AddItem "Complaint Closed - No Contact Process Followed"
        .AddItem "Complaint Closed - NFACC"
    [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Upvote 0
Thanks for the reply,
I have been looking at the issue for a while and as far as I can tell the combobox isnt being populated as the info it is checking to verify which options to offer isnt actually populated when the "userform_initialize()" is being initiated.

As soon as the code "Worked.Show False" is hit in the code the "userform_initialize()" starts, but the combobox1 isnt populated until the line "Worked.ETType.Value = Cells(iRow, 7)" which is the 3rd step in uploading the found information to the form.

Is there anyway to re run the "Sub UserForm_Initialize()" at the end to adjust the data that needs to be loaded into Combobox1 as it will then be able to read the "Complaint or Enquiry" option in the reference "ETType"

thanks
 
Upvote 0
I think I understand what you're doing now. It wasn't clear before.

In your original code, you didn't qualify the userform when you tested for the ETType to populate the combobox.

Code: 
If [B]Worked.[/B]ETType = "Complaint" Then
     With [B]Worked.[/B]ComboBox1
'
'
'
If [B]Worked.[/B]ETType = "Enquiry" Then
     With [B]Worked.[/B]ComboBox1


Below is your code cleaned up a bit (Not tested)

Code: 
    [color=darkblue]Dim[/color] iRow      [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] FYV_Sht   [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] MyEnquiryNo [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] MyFind    [color=darkblue]As[/color] [color=darkblue]Object[/color]
    
    Application.ScreenUpdating = [color=darkblue]False[/color]
    
    MyEnquiryNo = Sheets("Today").Range("C17").Value
    Sheet2.Visible = [color=darkblue]True[/color]
    
    [color=darkblue]If[/color] MyEnquiryNo = "" [color=darkblue]Then[/color]
        MsgBox "Please enter Reference number to search"
        Sheets("Today").Range("C17").Select
    [color=darkblue]Else[/color]
        [color=darkblue]Set[/color] FYV_Sht = ActiveWorkbook.Sheets("Import Open")
        FYV_Sht.Select
    
        [color=darkblue]Set[/color] MyFind = FYV_Sht.Columns(1).Find(What:=MyEnquiryNo, lookat:=xlWhole, SearchDirection:=xlNext)
        [color=darkblue]If[/color] [color=darkblue]Not[/color] MyFind [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
            iRow = MyFind.Row
    
            [color=darkblue]With[/color] Worked
                .txtdate.Value = Cells(iRow, 1)
                .txtdate.Locked = [color=darkblue]True[/color]
                .TextBox3.Value = Cells(iRow, 2)
                .ETType.Value = Cells(iRow, 7)
                .ETType.Locked = [color=darkblue]True[/color]
                .Rdate.Value = Cells(iRow, 3)
                .Rdate.Locked = [color=darkblue]True[/color]
                .TextBox4.Value = Cells(iRow, 8)
                .TextBox5.Value = Cells(iRow, 9)
                .TextBox6.Value = Cells(iRow, 10)
                .TextBox7.Value = Cells(iRow, 13)
                .TextBox2.Value = Cells(iRow, 14)
    
                [color=darkblue]With[/color] .ComboBox1
                    [color=darkblue]If[/color] Worked.ETType = "Complaint" [color=darkblue]Then[/color]
                        .AddItem "Complaint Pended - Awaiting Customer Info"
                        .AddItem "Complaint Pended - Awaiting Escalation work"
                        .AddItem "Complaint Pended - Awaiting Fault"
                        .AddItem "Complaint Pended - Awaiting Site Visit"
                        .AddItem "Complaint Pended - No Contact Process Initiated"
                        .AddItem "Complaint Pended - To work"
                        .AddItem "Complaint Closed - Assigned to Hit Team"
                        .AddItem "Complaint Closed - Customer Satisfied"
                        .AddItem "Complaint Closed - DEADLOCK"
                        .AddItem "Complaint Closed - No Contact Process Followed"
                        .AddItem "Complaint Closed - NFACC"
                    [color=darkblue]ElseIf[/color] Worked.ETType = "Enquiry" [color=darkblue]Then[/color]
                        .AddItem "Enquiry Pended - Awaiting Customer Info"
                        .AddItem "Enquiry Pended - Awaiting Escalation work"
                        .AddItem "Enquiry Pended - Awaiting Fault"
                        .AddItem "Enquiry Pended - Awaiting Site Visit"
                        .AddItem "Enquiry Pended - No Contact Process Initiated"
                        .AddItem "Enquiry Pended - To work"
                        .AddItem "Enquiry Closed - Assigned to Hit Team"
                        .AddItem "Enquiry Closed - Customer Satisfied"
                        .AddItem "Enquiry Closed - DEADLOCK"
                        .AddItem "Enquiry Closed - No Contact Process Followed"
                        .AddItem "Enquiry Closed - NFACC"
                    [color=darkblue]End[/color] [color=darkblue]If[/color]
                [color=darkblue]End[/color] [color=darkblue]With[/color]
    
                Sheet2.Visible = xlVeryHidden
                Sheet1.Select
                .Show [color=darkblue]False[/color]
            [color=darkblue]End[/color] [color=darkblue]With[/color]
    
        [color=darkblue]Else[/color]
            MsgBox "Unable to Find " & MyEnquiryNo & ". Please try another Complaint/Enquiry.", vbOKOnly, "Manager Not Found"
            Sheet2.Visible = xlVeryHidden
            Sheets("Today").Select
            Range("C17").Select
        [color=darkblue]End[/color] [color=darkblue]If[/color]
        
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Upvote 0
thanks for this, but I am getting a compile error @ "With .combobox1.

Any idea what is wrong?
 
Upvote 0
Thanks for the reply.
I do apologise, I left my laptop at work and didnt have a copy of the file at home.

It is showing a compile error "Else without if" at
Code: 
ElseIf Worked.ETType = "Enquiry" Then

Combobox1 is on the userform Worked as above and combobox1 is where the lists should appear

I have tried a couple of things, but have really used ElseIf
any help appreciated as I havent replied earlier
 
Upvote 0
Afternoon All,

I have amended the code above and it works now. I ended up changing the ElseIf section as per below

Code: 
If Worked.ETType = "Complaint" Then
                Worked.ComboBox1.AddItem "Complaint Pended - Awaiting Customer Info"
                Worked.ComboBox1.AddItem "Complaint Pended - Awaiting Escalation work"
                Worked.ComboBox1.AddItem "Complaint Pended - Awaiting Fault"
                Worked.ComboBox1.AddItem "Complaint Pended - Awaiting Site Visit"
                Worked.ComboBox1.AddItem "Complaint Pended - No Contact Process Initiated"
                Worked.ComboBox1.AddItem "Complaint Pended - To work"
                Worked.ComboBox1.AddItem "Complaint Closed - Assigned to Hit Team"
                Worked.ComboBox1.AddItem "Complaint Closed - Customer Satisfied"
                Worked.ComboBox1.AddItem "Complaint Closed - DEADLOCK"
                Worked.ComboBox1.AddItem "Complaint Closed - No Contact Process Followed"
                Worked.ComboBox1.AddItem "Complaint Closed - NFACC"
            End If
        If Worked.ETType = "Enquiry" Then
               Worked.ComboBox1.AddItem "Enquiry Pended - Awaiting Customer Info"
               Worked.ComboBox1.AddItem "Enquiry Pended - Awaiting Escalation work"
               Worked.ComboBox1.AddItem "Enquiry Pended - Awaiting Fault"
               Worked.ComboBox1.AddItem "Enquiry Pended - Awaiting Site Visit"
               Worked.ComboBox1.AddItem "Enquiry Pended - No Contact Process Initiated"
               Worked.ComboBox1.AddItem "Enquiry Pended - To work"
               Worked.ComboBox1.AddItem "Enquiry Closed - Assigned to Hit Team"
               Worked.ComboBox1.AddItem "Enquiry Closed - Customer Satisfied"
               Worked.ComboBox1.AddItem "Enquiry Closed - DEADLOCK"
               Worked.ComboBox1.AddItem "Enquiry Closed - No Contact Process Followed"
               Worked.ComboBox1.AddItem "Enquiry Closed - NFACC"
            End If
            
        Else

thanks for the help all
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Opening userform closes excel and can't open other workbooks
Replies
3
Views
844
NHagedorn
N
B
  • Question Question
Issue with UserForms code to apply data from the form to Excel file
Replies
5
Views
1K
Yongle
Yongle
S
  • Question Question
Userform intermittently not loading and then causing an issue with constant calculation.
Replies
8
Views
1K
Snayff
S
G
  • Question Question
Create a secure Password
Replies
1
Views
470
shg
shg
G
  • Question Question
search from the top instead of the bottom
Replies
0
Views
482
gmazza76
G

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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