Listview in userform to show data entred by textboxes and comboboxes

**djemy1975** · 01-19-2016, 02:27 AM

Listview in userform to show data entred by textboxes and comboboxes

What I want to do here is to show data as soon as they are entered into worksheet .This step is already done,what remains is to show the data on listview as they are entered to make sure the data are already entered.I have this piece of code so far:

Private Sub cmdAdd_Click()
Dim ws As Worksheet
Dim FoundCell As Range
Dim Search As String
Set ws = Worksheets("STORAGE")
eRow = ws.Cells(Rows.Count, 2).End(xlUp).Offset(2, 0).Row
Me.Reg7.SetFocus


'Fill listview2 when data entered






'Fill Worksheet "STORAGE" with data entered via userform


eRow = Worksheets("STORAGE").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Search = Reg7.Text
Set FoundCell = Worksheets("STORAGE").Columns(6).Find(Search, LookIn:=xlValues, Lookat:=xlWhole)
If FoundCell Is Nothing Then
MsgBox "Numéro de facture n'existe pas sur la base de donnée! Vous pouvez continuer"
ws.Cells(eRow, 6).Value = Me.Reg7.Value
ws.Cells(eRow, 2).Value = Me.DTPicker1.Value
ws.Cells(eRow, 5).Value = Me.Reg8.Value
ws.Cells(eRow, 3).Value = Me.Reg9.Value
ws.Cells(eRow, 7).Value = Me.Reg10.Value
ws.Cells(eRow, 8).Value = Me.Reg11.Value
ws.Cells(eRow, 4).Value = Me.Reg1.Value
ws.Cells(eRow, 1).Value = Me.Reg2.Value
Else
MsgBox "N° de facture existe!" & " Données sur la plage " & FoundCell.Address & " Veuillez Refaire le N° de Facture"
End If


End With
End Sub

Thanks in advance

**SamT** · 01-19-2016, 03:54 AM

Search is a Function in Excel, Suggest you edit to SearchTerm

Private Function Simple_AllDataEntered()
Simple_AllDataEntered = True

With Me
      If .Reg7= "" Then
Simple_AllDataEntered = False
        ElseIf .DTPicker1 = "" Then
Simple_AllDataEntered = False
        ElseIf .Reg8 = "" Then
Simple_AllDataEntered = False
        ElseIf .Reg9 = "" Then
Simple_AllDataEntered = False
        ElseIf .Reg10 = "" Then
Simple_AllDataEntered = False 
        ElseIf .Reg11 = "" Then
Simple_AllDataEntered = False
        ElseIf .Reg1 = "" Then
Simple_AllDataEntered = False
        ElseIf .Reg2 = "" Then
Simple_AllDataEntered = False
End If
End With
End Function

Private Sub cmdAdd_Click() 
    Dim ws As Worksheet 
    Dim FoundCell As Range 
    Dim Search As String 

'Add this
If Not Simple_AllDataEntered Then 
MsgBox "please Enter missing data"
Exit Sub
End If
'End Add This

    Set ws = Worksheets("STORAGE") 
    eRow = ws.Cells(Rows.Count, 2).End(xlUp).Offset(2, 0).Row 
    Me.Reg7.SetFocus

**snb** · 01-19-2016, 04:00 AM

I don't think so, @SamT (too many 'dots'):

Private Function F_complete() 
   F_complete = (Reg7<> "" )*(DTPicker1<> "")*(Reg8.<> "")*(Reg9<> "")*(Reg10<> "")*(Reg11<> "")*(Reg1<> "")*(Reg2<> "")
End Function

**djemy1975** · 01-19-2016, 04:36 AM

First of all thank you for your quick reply ,but unfortunaltely I have got an error im my project

An error has occurred. Error -2147221231 : ClassFactory cannot supply requested class

could someone tell me the source of this problem?

**SamT** · 01-19-2016, 04:45 AM

So?

Private Function Simple_AllDataEntered() 
    Simple_AllDataEntered = True 
     
         If Reg7= "" Then 
            Simple_AllDataEntered = False 
        ElseIf DTPicker1 = "" Then 
            Simple_AllDataEntered = False 
        ElseIf Reg8 = "" Then 
            Simple_AllDataEntered = False 
        ElseIf Reg9 = "" Then 
            Simple_AllDataEntered = False 
        ElseIf Reg10 = "" Then 
            Simple_AllDataEntered = False 
        ElseIf Reg11 = "" Then 
            Simple_AllDataEntered = False 
        ElseIf Reg1 = "" Then 
            Simple_AllDataEntered = False 
        ElseIf Reg2 = "" Then 
            Simple_AllDataEntered = False 
        End If 
End Function

Doesn't this give the same result?

Private Function F_complete() 
    F_complete = Not Reg7<> "" - DTPicker1<> "" - Reg8 <> "" - Reg9<> "" - Reg10<> "" - Reg11<> "" - Reg1<> "" - Reg2<> "" 
End Function

**snb** · 01-19-2016, 04:45 AM

If you show us your project.

**SamT** · 01-19-2016, 04:46 AM

An error has occurred. Error -2147221231 : ClassFactory cannot supply requested class

could someone tell me the source of this problem?

Need more info

**snb** · 01-19-2016, 04:50 AM

Originally Posted by SamT

So?[CODE]

Doesn't this give the same result?

Private Function F_complete() 
    F_complete = Not Reg7<> "" - DTPicker1<> "" - Reg8 <> "" - Reg9<> "" - Reg10<> "" - Reg11<> "" - Reg1<> "" - Reg2<> "" 
End Function

I don't think so :
x*y*z means x= true and y=true and z=true

x + Y + Z means x=true or y =true or z =true

**djemy1975** · 01-19-2016, 04:55 AM

class.jpg

Originally Posted by SamT

Need more info

**djemy1975** · 01-19-2016, 04:57 AM

How can I attach xls file

**SamT** · 01-19-2016, 05:30 AM

How can I attach xls file

Use the "Go Advanced" Option, Below that editor, use the Manage Attachments button

**SamT** · 01-19-2016, 05:41 AM

@ snb,
Right

Function = Not a ="" + B = "" + C = ""

Shaddup you, I bin up all night.

**djemy1975** · 01-19-2016, 05:45 AM

herewith my project.Hope you can help me go further

Thanks in advance

**SamT** · 01-19-2016, 06:06 AM

You are really depending on the ListView control. Unfortunately I don't have it.
Good luck.

You won't need it if you use snb's or my code. To replace all the ListView code, all you might need is

Private Sub cmdAdd_Click() 
    Dim ws As Worksheet 
    Dim FoundCell As Range 
    Dim Search As String 

If  Not  F_complete Then 
        MsgBox "please Enter missing data" 
        Exit Sub 
    End If 

 Set ws = Worksheets("STORAGE") 
    eRow = ws.Cells(Rows.Count, 2).End(xlUp).Offset(2, 0).Row 
    Me.Reg7.SetFocus 
     
    eRow = Worksheets("STORAGE").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row 
    Search = Reg7.Text 
    Set FoundCell = Worksheets("STORAGE").Columns(6).Find(Search, LookIn:=xlValues, Lookat:=xlWhole) 
    If FoundCell Is Nothing Then 
        MsgBox "Numéro de facture n'existe pas sur la base de donnée! Vous pouvez continuer" 
        ws.Cells(eRow, 6).Value = Me.Reg7.Value 
        ws.Cells(eRow, 2).Value = Me.DTPicker1.Value 
        ws.Cells(eRow, 5).Value = Me.Reg8.Value 
        ws.Cells(eRow, 3).Value = Me.Reg9.Value 
        ws.Cells(eRow, 7).Value = Me.Reg10.Value 
        ws.Cells(eRow, 8).Value = Me.Reg11.Value 
        ws.Cells(eRow, 4).Value = Me.Reg1.Value 
        ws.Cells(eRow, 1).Value = Me.Reg2.Value 
    Else 
        MsgBox "N° de facture existe!" & " Données sur la plage " & FoundCell.Address & " Veuillez Refaire le N° de Facture" 
    End If 
     
     
End With 
End Sub 

Private Function F_complete() 
    F_complete = (Reg7<> "" )*(DTPicker1<> "")*(Reg8.<> "")*(Reg9<> "")*(Reg10<> "")*(Reg11<> "")*(Reg1<> "")*(Reg2<> "") 
End Function

**djemy1975** · 01-19-2016, 06:14 AM

Thank you sir.I will test it without listview and if there is a possibility to use listview do not hesitate to provide me with A it gives a handsome look for the application

**djemy1975** · 01-19-2016, 06:35 AM

It keeps giving message"please Enter missing data" though all controls are filled in

**djemy1975** · 01-19-2016, 07:26 AM

Can I replace Listview with listbox as the source of error is coming from listview control not correctly registered

**djemy1975** · 01-19-2016, 07:31 AM

What I have depending on your code is that

Could please help me now by adding some features to my listbox and revising the code

**SamT** · 01-19-2016, 12:58 PM

In snb's function remove the dot by reg8.

**snb** · 01-19-2016, 01:10 PM

Originally Posted by SamT

@ snb,
Right

Function = Not a ="" + B = "" + C = ""

Shaddup you, I bin up all night.

Why did you ???

Thread: Listview in userform to show data entred by textboxes and comboboxes

Thread Tools

Display

Listview in userform to show data entred by textboxes and comboboxes

My Project

Posting Permissions