PDA

View Full Version : [SOLVED] 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
So?


Doesn't this give the same result?
[CODE]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
15211
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 fileUse 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. :cuckoo:

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
@ snb,
Right


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

Shaddup you, I bin up all night. :cuckoo:

Why did you ???

SamT
01-19-2016, 01:47 PM
No diurnal rhythm

djemy1975
01-19-2016, 02:02 PM
still giving this message though all contriols are filled in "please Enter missing data"

SamT
01-19-2016, 03:48 PM
Try
Function F_complete() as boolean
Done = Len(Reg7) * Len(Reg8) * Len(Reg9) * Len(Reg10) 'Etc
End Function

As many times as you refer to those controls, consider this
Dim IOControls As Collection 'Module Variable

Sub UserForm_Initialize()
With IOControls
.Add Me.Controls("Reg7")
.Add Me.Controls("Reg8")
.Add Me.Controls("Reg9")
'Etc
End Sub

Then you can do
Function F_complete() as boolean
Dim Ctrl As Object
F_complete = True
For Each Ctrl In IOControls
F_complete = F_complete * Len(Ctrl)
Next
End Function

If you also have
Dim ECols As Variant

Sub UserForm_Initialize()
Ecols = Array(6, 2, 5,,,,,) 'etc
'
'
'
End Sub
Then you can

If FoundCell Is Nothing Then
MsgBox "Numéro de facture n'existe pas sur la base de donnée! Vous pouvez continuer"
For i = 0 to 7
ws.Cells(eRow, ECols(i)).Value = IOControls(i + 1)
Next
Else
'
'
'
End IF

djemy1975
01-20-2016, 12:47 AM
Good morning,

Are those codes meant for file with listbox or listview and how to use them?

Thanks,

djemy1975
01-20-2016, 03:32 AM
I have used a combination of your code and another code using the listview .I was able to view data on listview as soon as I entered them but when I load the userform next time it initializes empty.Is there any way to load data formerly entered and add data at the same time (enter data+view previously entered data).Herewith a sample of my file.

THANKS IN ADVANCE

SamT
01-20-2016, 12:16 PM
See this link
Items (https://msdn.microsoft.com/en-us/library/system.windows.forms.listview.items%28v=vs.90%29.aspx)
From this page
https://msdn.microsoft.com/en-us/library/ms172636%28v=vs.90%29.aspx

Then something like

Dim CurrentListITems As Collection

Sub Example_SaveItems()
CurrentiLisItems = List.Items
End Sub

Sub ExampleReinstateItems()
With List
.Items = CurrentsListItems

Set .Items = CurrentsListItems

For Each It in CurrentsListItems
.Items. Add It
Next It

.Items.Add blah blah
End Sub

djemy1975
01-21-2016, 12:52 AM
can't figure out how to use those codes..could you apply them on my codes

djemy1975
01-21-2016, 02:37 AM
Here is my file to apply code.

Thanks in advance



See this link
Items (https://msdn.microsoft.com/en-us/library/system.windows.forms.listview.items%28v=vs.90%29.aspx)
From this page
https://msdn.microsoft.com/en-us/library/ms172636%28v=vs.90%29.aspx

Then something like

Dim CurrentListITems As Collection

Sub Example_SaveItems()
CurrentiLisItems = List.Items
End Sub

Sub ExampleReinstateItems()
With List
.Items = CurrentsListItems

Set .Items = CurrentsListItems

For Each It in CurrentsListItems
.Items. Add It
Next It

.Items.Add blah blah
End Sub

djemy1975
02-01-2016, 07:45 AM
Hello again,

Is there any way to format "date" and amounts inside a listview .Here is my sample data.

Thanks in advance,

SamT
02-01-2016, 10:34 AM
All values in a ListView are Strings, therefore you must format numerical values before they are listed.

If IsDate(Value) then Value = Format(Value, "dd/mmm/yy")
ElseIs IsNumerical(Value) Then Value = Format(Value, "$#####.##")
End If

djemy1975
02-02-2016, 01:04 AM
Good morning,

First of all ,thank you for your help ,but could you tell me where to put the code
If IsDate(Value) Then Value = Format(Value, "dd/mmm/yy")
ElseIf IsNumerical(Value) Then Value = Format(Value, "$#####.##")
End If

djemy1975
02-02-2016, 01:09 AM
It gives an error "sub or function not defined"

djemy1975
02-02-2016, 02:25 AM
Hi again,

I have completely modified the code to initialize the date format but the code is working on all columns .Could you help me to set only for the sixth column.Herewith my sample modified

Thanks in advance,

SamT
02-02-2016, 10:43 AM
' If IsDate(ActiveSheet.Cells(i, .ColumnHeaders(j).Tag).Value) Then ' Problème!!!!
.ListItems(.ListItems.Count).ListSubItems.Add , , Format(ActiveSheet.Cells(i, .ColumnHeaders(j).Tag).Value, "DDDD DD MMMM YYYY")
' End If

Something like this

' If IsDate(ActiveSheet.Cells(i, .ColumnHeaders(j).Tag).Value) Then ' Problème!!!!
If .ColumnHeaders(j).Tag = 6 then
.ListItems(.ListItems.Count).ListSubItems.Add , , Format(ActiveSheet.Cells(i, .ColumnHeaders(j).Tag).Value, "DDDD DD MMMM YYYY")
Else
.ListItems(.ListItems.Count).ListSubItems.Add , , Cells(i, .ColumnHeaders(j).Tag).Value
End if
' End If

djemy1975
02-02-2016, 01:25 PM
thank you very much it worked as a charm

snb
02-02-2016, 01:28 PM
Pas de problème:


Private Sub UserForm_Initialize()
sn = Sheets("BDD").Cells(1).CurrentRegion

With ListView3
.View = 3
.Gridlines = True
.FullRowSelect = True

For jj = 1 To UBound(sn, 2)
.ColumnHeaders.Add(, , sn(1, jj), 100, 0).Tag = jj
Next

For j = 1 To UBound(sn)
With .ListItems.Add(, , sn(j, 1))
For jj = 2 To UBound(sn, 2)
.ListSubItems.Add , , sn(j, jj)
Next
End With
Next
End With
End Sub

djemy1975
02-03-2016, 01:40 AM
Good morning,

I have added your piece of code and I am so happy to get such kindness of you .As you know I am developping an excel vba invoicing application and from time to time I got stuck in some obstacles going further and fortunately you are backing me up so far.The next step in my application is to copy specific columns via userform (Userform1) in an output "Facture" based on selection in combobox starting at row B18.The result is shown in listview4 and calculations in textboxes .
Calculations:

HT=Prix unitaire*Quantité
TVA=HT*17/100
TTC=tva+ht

Is it possible?Herewith my sample to apply mods on

djemy1975
02-04-2016, 07:29 AM
Hi,

I nearly got the solution ,but what is missing is to copy from listview to sheet"Facture " in specific cells highlighted with yellow colour.

herewith my file so far: