Help Please .....

AsifShah

New Member
Joined
Feb 12, 2021
Messages
20
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
Dear Friends
In image 1 i have created a userform to save my invoice data,

Dim dcc As Long
Dim abc As Worksheet
Set abc = Worksheets
i was use this DIM commond to save my data. but now i was created a new userform Check image 2.
in New UeserForm i want to search my Product by Product Code through Combobox
For Example:
When i type Product Code "1" in Combobox1 and Qty in (TextBox1) then Only my Qty Value save in Sheet2 ColumeA7 But When
When i type Product Code "2" in Combobox1 and Qty in (TextBox1) then Only my Qty Value save in Sheet2 ColumeB7
When i type Product Code "3" in Combobox1 and Qty in (TextBox1) then Only my Qty Data save in Sheet2 ColumeC7
When i type Product Code "4" in Combobox1 and Qty in (TextBox1) then Only my Qty Data save in Sheet2 ColumeD8.....................ETC

And also save my Customer Details and Date
Please Help.....

Image 1

pic1.png

image2
UserForm.png
 

fadee2

Active Member
Joined
Nov 7, 2020
Messages
367
Office Version
  1. 2019
Platform
  1. Windows
I had some time to spare so, I redesigned your dispatch form from
1613564252186.png

to
1613564437285.png

As I said earlier there is no need to have so many controls when all of this can be achieved with just a few.
made a few adjustments to your product names in Dispatches Detail sheet. The names from R-List sheet replaced old names.

NewTexting.xlsm
FGHIJKLMNOPQRSTUVWXYZAAABACADAE
6RGB CokeRGB SpriteRGB FantaTotal1500ML COKE1500ML SPRITE1500ML FANTATotal2250ML COKE2250ML SPRITE2250ML FANTATotal1000ML COKE1000ML SPRITE1000ML FANTATotal500ML COKE500ML SPRITE500ML FANTATotal250ML SC COKE250ML SC SPRITE250ML SC FANTATotalDASANI 1500MLDASANI 500ML
Dispatches Detail


After this added below two codes to control buttons "OK" and "Save Data", respectively.

VBA Code:
Private Sub CommandButton4_Click()
icount = ListBox1.ListCount

With ListBox1
   
    .AddItem
    .ColumnCount = 8
   
        .TextAlign = 2
       
        If icount = 0 Then
            .List(icount, 0) = icount + 1
        Else
            .List(icount, 0) = icount + 1
        End If
       
        .List(icount, 1) = ComboBox1.Value
        .List(icount, 2) = TextBox1.Text
        .List(icount, 3) = TextBox2.Text
        .List(icount, 4) = TextBox3.Text
        .List(icount, 5) = TextBox4.Text
        .List(icount, 6) = TextBox5.Text
        .List(icount, 7) = TextBox6.Text

         j = .List(icount, 6)
         k = .List(icount, 7)
End With

icount = ListBox1.ListCount
If icount = 0 Then
    Label162.Caption = 1
Else
    Label162.Caption = icount + 1
End If

ComboBox1.Value = ""
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""


TextBox126.Text = Val(TextBox126.Text) + j
TextBox125.Text = Val(TextBox125.Text) + k

icount = ""
CommandButton4.Enabled = False
End Sub

Private Sub cmd_save_Click()
Dim rng As Range
Set rng = Sheets("Dispatches Detail").Range("a6:af5")

lr = Sheets("dispatches detail").Cells(Rows.Count, 1).End(xlUp).Row
lr = lr + 1

If Sheets("dispatches detail").Cells(lr - 1, 1) = "SR No" Then
    Sheets("dispatches detail").Cells(lr, 1) = 1
Else
    Sheets("dispatches detail").Cells(lr, 1) = Sheets("dispatches detail").Cells(lr - 1, 1) + 1
End If

For icount = 0 To ListBox1.ListCount - 1
    With ListBox1

        t = .List(icount, 2)
        s = rng.Find(what:=t).Column
  
        Sheets("dispatches detail").Cells(lr, s).Select
        Sheets("dispatches detail").Cells(lr, s) = .List(icount, 6)
      
    End With
Next icount

icount = ""
lr = ""
End Sub

Rename your product names as above and use above 2 codes.

hth....
 
Last edited:
Solution

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

AsifShah

New Member
Joined
Feb 12, 2021
Messages
20
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
I had some time to spare so, I redesigned your dispatch form from
View attachment 32327
to
View attachment 32329
As I said earlier there is no need to have so many controls when all of this can be achieved with just a few.
made a few adjustments to your product names in Dispatches Detail sheet. The names from R-List sheet replaced old names.

NewTexting.xlsm
FGHIJKLMNOPQRSTUVWXYZAAABACADAE
6RGB CokeRGB SpriteRGB FantaTotal1500ML COKE1500ML SPRITE1500ML FANTATotal2250ML COKE2250ML SPRITE2250ML FANTATotal1000ML COKE1000ML SPRITE1000ML FANTATotal500ML COKE500ML SPRITE500ML FANTATotal250ML SC COKE250ML SC SPRITE250ML SC FANTATotalDASANI 1500MLDASANI 500ML
Dispatches Detail


After this added below two codes to control buttons "OK" and "Save Data", respectively.

VBA Code:
Private Sub CommandButton4_Click()
icount = ListBox1.ListCount

With ListBox1
  
    .AddItem
    .ColumnCount = 8
  
        .TextAlign = 2
      
        If icount = 0 Then
            .List(icount, 0) = icount + 1
        Else
            .List(icount, 0) = icount + 1
        End If
      
        .List(icount, 1) = ComboBox1.Value
        .List(icount, 2) = TextBox1.Text
        .List(icount, 3) = TextBox2.Text
        .List(icount, 4) = TextBox3.Text
        .List(icount, 5) = TextBox4.Text
        .List(icount, 6) = TextBox5.Text
        .List(icount, 7) = TextBox6.Text

         j = .List(icount, 6)
         k = .List(icount, 7)
End With

icount = ListBox1.ListCount
If icount = 0 Then
    Label162.Caption = 1
Else
    Label162.Caption = icount + 1
End If

ComboBox1.Value = ""
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""


TextBox126.Text = Val(TextBox126.Text) + j
TextBox125.Text = Val(TextBox125.Text) + k

icount = ""
CommandButton4.Enabled = False
End Sub

Private Sub cmd_save_Click()
Dim rng As Range
Set rng = Sheets("Dispatches Detail").Range("a6:af5")

lr = Sheets("dispatches detail").Cells(Rows.Count, 1).End(xlUp).Row
lr = lr + 1

If Sheets("dispatches detail").Cells(lr - 1, 1) = "SR No" Then
    Sheets("dispatches detail").Cells(lr, 1) = 1
Else
    Sheets("dispatches detail").Cells(lr, 1) = Sheets("dispatches detail").Cells(lr - 1, 1) + 1
End If

For icount = 0 To ListBox1.ListCount - 1
    With ListBox1

        t = .List(icount, 2)
        s = rng.Find(what:=t).Column
 
        Sheets("dispatches detail").Cells(lr, s).Select
        Sheets("dispatches detail").Cells(lr, s) = .List(icount, 6)
     
    End With
Next icount

icount = ""
lr = ""
End Sub

Rename your product names as above and use above 2 codes.

hth....
Fahad Bro Thanks Alottttt ... My Problem is Solved .
Bundle of Thanks
 

fadee2

Active Member
Joined
Nov 7, 2020
Messages
367
Office Version
  1. 2019
Platform
  1. Windows
you are welcome... and thanks for the feedback
 

Forum statistics

Threads
1,141,058
Messages
5,704,031
Members
421,323
Latest member
Exidous

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
Top