UPSC :-UPSC Prelims 2021 Paper Analysis I Indian Polity UPSC Prelims 2021 Paper Analysis I Science & Technology UPSC Prelims 2021 Paper Analysis I Geography IAS/PCS Essay Book Paper Notice and syllabus :PET EXAMINATION SYLLABUS PET QUESTION PAPER SINDHU GHATI SABHYATA GST B.Ed Second Year Book -2021 DIGITAL INDIA CHILD LABOUR IN INDIA Competitive Book :- GK/GS Book 2021 All Competitive Maths Book All Competitive Resigning Book Important Project :-Visual Studio C # Project Tally Erp9 Account Feature Microsoft Excel VBA Project who to apply mathematical Formula in ms excel user form MS Excel UserForm Save And Edit Button Part-1 Module MS Excel UserForm Save And Edit Button Part-2 Module

Micro Soft VBA Project

GRIDLINE THE MS EXCEL SHEET VIDEO

Table of Contect:-

2. User Form ToolBox

3. User Form Properties

4. User Form Button Module

i) Add Data Module

ii) Edit Data Module

iii) Delete Data Module

iv) Next and Preview Module

v) ListBox Module

vi) User Form Ittlige Module

vii) Currant Data Add Module

viii) Preview and Next Button Module

5. Log In Form Design

i) Log in Button Module with security

Log In Form Design

..................................................................

let :-

TextBox1 Name = TextBoxId

TextBox2 Name = TextBoxPassword

Button1 Name = Log_In

Button2 Name = Cancel

Module:-

Private Sub CommandButtonLog_In_Click()

If TextBoxId.Text = "super" And TextBoxPassword.Text = "123" Then

MsgBox "LogIn successful"

UserFormMenu.Show

Else

TextBoxId.Text = ""

TextBoxPassword.Text = ""

MsgBox "Log in Incorrect Please Registred your Company"

UserFormCard_Master.Show

End If

End Sub

1) User Form Preview and Next Button Design

......................................................................................................

Let:-

Toggle Button1 = ToggleButtonPreview

Toggle Button 2 = ToggleButtonNext

Text Box 1 = TextBoxPos_Entry_Id ...........................(visuality = False)

Text Box 2 = TextBoxPosEntryBillPrint

Text Box 3 = TextBoxPosEntryBarcode

Text Box 4 = TextBoxPosEntryQty

Text Box 5 = TextBoxPosEntryRate

Text Box 6 = TextBoxPosEntryDisP

Text Box 7 = TextBoxPosEntryDiscount

Text Box 8 = TextBoxPosEntrySalesMan

List Box 1 = ListBoxPos_Entry_Return_Detail

Preview Button Module

........................................................................

Private Sub ToggleButtonPreview_Click()

Dim i As Integer

If Me.TextBoxPos_Entry_Id.Value = "" Then

msgbox " Please Select a Item"

Exit Sub

End If

i = Me.ListBoxPos_Entry_Return_Detail.ListIndex

Me.ListBoxPos_Entry_Return_Detail.Selected(i) = True

Me.TextBoxPos_Entry_Id.Value = Me.ListBoxPos_Entry_Return_Detail.Column(0, i - 1)

Me.TextBoxPosEntryBillPrint.Value = Me.ListBoxPos_Entry_Return_Detail.Column(1, i - 1)

Me.TextBoxPosEntryBarcode.Value = Me.ListBoxPos_Entry_Return_Detail.Column(3, i - 1)

Me.TextBoxPosEntryQty.Value = Me.ListBoxPos_Entry_Return_Detail.Column(6, i - 1)

Me.TextBoxPosEntryRate.Value = Me.ListBoxPos_Entry_Return_Detail.Column(8, i - 1)

Me.TextBoxPosEntryDisP.Value = Me.ListBoxPos_Entry_Return_Detail.Column(9, i - 1)

Me.TextBoxPosEntryDiscount.Value = Me.ListBoxPos_Entry_Return_Detail.Column(10, i - 1)

Me.TextBoxPosEntrySalesMan.Value = Me.ListBoxPos_Entry_Return_Detail.Column(12, i - 1)

Me.ListBoxPos_Entry_Return_Detail.Selected(i - 1) = True

End Sub

Next Button Module

.............................................................

Private Sub ToggleButtonNext_Click()

Dim i As Integer

If Me.TextBoxPos_Entry_Id.Value = "" Then

msgbox " Please Select the list Item"

Exit Sub

End If

i = Me.ListBoxPos_Entry_Return_Detail.ListIndex

Me.ListBoxPos_Entry_Return_Detail.Selected(i) = True

Me.TextBoxPos_Entry_Id.Value = Me.ListBoxPos_Entry_Return_Detail.Column(0, i + 1)

Me.TextBoxPosEntryBillPrint.Value = Me.ListBoxPos_Entry_Return_Detail.Column(1, i + 1)

Me.TextBoxPosEntryBarcode.Value = Me.ListBoxPos_Entry_Return_Detail.Column(3, i + 1)

Me.TextBoxPosEntryQty.Value = Me.ListBoxPos_Entry_Return_Detail.Column(6, i + 1)

Me.TextBoxPosEntryRate.Value = Me.ListBoxPos_Entry_Return_Detail.Column(8, i + 1)

Me.TextBoxPosEntryDisP.Value = Me.ListBoxPos_Entry_Return_Detail.Column(9, i + 1)

Me.TextBoxPosEntryDiscount.Value = Me.ListBoxPos_Entry_Return_Detail.Column(10, i + 1)

Me.TextBoxPosEntrySalesMan.Value = Me.ListBoxPos_Entry_Return_Detail.Column(12, i + 1)

Me.ListBoxPos_Entry_Return_Detail.Selected(i + 1) = True

End Sub

List Box Click Module

................................................................

Private Sub ListBoxPos_Entry_Return_Detail_Click()

Dim i As Integer

i = Me.ListBoxPos_Entry_Return_Detail.ListIndex

Me.ListBoxPos_Entry_Return_Detail.Selected(i) = True

Me.TextBoxPos_Entry_Id.Value = Me.ListBoxPos_Entry_Return_Detail.Column(0, i)

Me.TextBoxPosEntryBillPrint.Value = Me.ListBoxPos_Entry_Return_Detail.Column(1, i)

Me.TextBoxPosEntryBarcode.Value = Me.ListBoxPos_Entry_Return_Detail.Column(3, i)

Me.TextBoxPosEntryQty.Value = Me.ListBoxPos_Entry_Return_Detail.Column(6, i)

Me.TextBoxPosEntryRate.Value = Me.ListBoxPos_Entry_Return_Detail.Column(8, i)

Me.TextBoxPosEntryDisP.Value = Me.ListBoxPos_Entry_Return_Detail.Column(9, i)

Me.TextBoxPosEntryDiscount.Value = Me.ListBoxPos_Entry_Return_Detail.Column(10, i)

Me.TextBoxPosEntrySalesMan.Value = Me.ListBoxPos_Entry_Return_Detail.Column(12, i)

End Sub

2) Add and Edit Button Module

.....................................................................................

Let :-

Command Button 1 = CommandButtonPosEntrySave

Private Sub CommandButtonPosEntrySave_Click()

Dim MsgValue As VbMsgBoxResult

MsgValue = msgbox("Do You Want to save the Date?", vbYesNo + vbInformation, "Confirmation")

If MsgValue = vbNo Then Exit Sub

'''''''''''validation'''''''''''

If Me.TextBoxPosEntryBarcode.Value = "" Then

msgbox "Please Enter the Product Name", vbCritical

Exit Sub

End If

If IsNumeric(Me.TextBoxPosEntryQty.Value) = False Then

msgbox "Please Enter the Correct Purchase Price", vbCritical

Exit Sub

End If

If IsNumeric(Me.TextBoxPosEntryRate.Value) = False Then

msgbox "Please Enter the Correct Sale Price", vbCritical

Exit Sub

End If

''''''''''''Add Data

Dim lr As Integer

Set sh = ThisWorkbook.Sheets("Pos_Entry_Return")

lr = Application.WorksheetFunction.CountA(sh.Range("AQ:AQ"))

sh.Range("AQ" & lr + 1).Value = lr

sh.Range("AR" & lr + 1).Value = Me.TextBoxPosEntryBillPrint.Value

sh.Range("AT" & lr + 1).Value = Me.TextBoxPosEntryBarcode.Value

sh.Range("AW" & lr + 1).Value = Me.TextBoxPosEntryQty.Value

sh.Range("AY" & lr + 1).Value = Me.TextBoxPosEntryRate.Value

sh.Range("AZ" & lr + 1).Value = Me.TextBoxPosEntryDisP.Value

sh.Range("BA" & lr + 1).Value = Me.TextBoxPosEntryDiscount.Value

sh.Range("BC" & lr + 1).Value = Me.TextBoxPosEntrySalesMan.Value

''''''''''Clear Boxes

Me.TextBoxPosEntryBillPrint.Value = ""

Me.TextBoxPosEntryBarcode.Value = ""

Me.TextBoxPosEntryQty.Value = ""

Me.TextBoxPosEntryRate.Value = ""

Me.TextBoxPosEntryDisP.Value = ""

Me.TextBoxPosEntryDiscount.Value = ""

Me.TextBoxPosEntrySalesMan.Value = ""

Call Show_Data

msgbox "Product has been added in Product Master", vbInformation

End Sub

Show Data in List Box Module

.................................................................................

Sub Show_Data()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Pos_Entry_Return")

Dim lr As Integer

lr = Application.WorksheetFunction.CountA(sh.Range("AQ:AQ"))

If lr = 1 Then lr = 2

With Me.ListBoxPos_Entry_Return_Detail

.ColumnCount = 14

.ColumnHeads = True

.ColumnWidths = "50,50,50,100,130,130,100,100,100,100,100,100,80,80"

.RowSource = "Pos_Entry_Return!AQ2:BE" & lr

End With

End Sub

List Box Dubble Click Module

.......................................................................

Private Sub ListBoxPos_Entry_Return_Detail_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Me.TextBoxPos_Entry_Id.Value = Me.ListBoxPos_Entry_Return_Detail.List(Me.ListBoxPos_Entry_Return_Detail.ListIndex, 0)

Me.TextBoxPosEntryBillPrint.Value = Me.ListBoxPos_Entry_Return_Detail.List(Me.ListBoxPos_Entry_Return_Detail.ListIndex, 1)

Me.TextBoxPosEntryBarcode.Value = Me.ListBoxPos_Entry_Return_Detail.List(Me.ListBoxPos_Entry_Return_Detail.ListIndex, 3)

Me.TextBoxPosEntryQty.Value = Me.ListBoxPos_Entry_Return_Detail.List(Me.ListBoxPos_Entry_Return_Detail.ListIndex, 6)

Me.TextBoxPosEntryRate.Value = Me.ListBoxPos_Entry_Return_Detail.List(Me.ListBoxPos_Entry_Return_Detail.ListIndex, 8)

Me.TextBoxPosEntryDisP.Value = Me.ListBoxPos_Entry_Return_Detail.List(Me.ListBoxPos_Entry_Return_Detail.ListIndex, 9)

Me.TextBoxPosEntryDiscount.Value = Me.ListBoxPos_Entry_Return_Detail.List(Me.ListBoxPos_Entry_Return_Detail.ListIndex, 10)

Me.TextBoxPosEntrySalesMan.Value = Me.ListBoxPos_Entry_Return_Detail.List(Me.ListBoxPos_Entry_Return_Detail.ListIndex, 12)

End Sub

User Form Click Module

...................................................................

Private Sub UserForm_Activate()

Call Show_Data

End Sub

UserForm_Initialize Module ( Show Current Date Module)

....................................................................................................................

Private Sub UserForm_Initialize()

Me.TextBoxTaxtable_Bill_Date.Value = Format(Date, "D-MMM-YYYY")

Call Add_Product_List

End Sub

CamboBox Product List Module

............................................................................

Let :-

CamboBox1 = ComboBoxItem_Grade

CamboBox 2 = ComboBoxHsn_Code

Sub Add_Product_List()

Set sh = ThisWorkbook.Sheets("Product")

Dim i As Integer

Me.ComboBoxItem_Grade.Clear

Me.ComboBoxItem_Grade.AddItem ""

For i = 2 To Application.WorksheetFunction.CountA(sh.Range("A:A"))

Me.ComboBoxItem_Grade.AddItem sh.Range("B" & i)

Next i

Set dsh = ThisWorkbook.Sheets("Product")

Dim j As Integer

Me.ComboBoxHsn_Code.Clear

Me.ComboBoxHsn_Code.AddItem ""

For j = 2 To Application.WorksheetFunction.CountA(sh.Range("A:A"))

Me.ComboBoxHsn_Code.AddItem dsh.Range("F" & j)

Next j

End Sub

Edit Button Module

........................................................

Let :

Command Button 2 = CommandButtonPosEntryEdit

Private Sub CommandButtonPosEntryEdit_Click()

'''''''''''validation'''''''''''

If Me.TextBoxPosEntryBarcode.Value = "" Then

msgbox "Please Enter the Product Name", vbCritical

Exit Sub

End If

If IsNumeric(Me.TextBoxPosEntryQty.Value) = False Then

msgbox "Please Enter the Correct Purchase Price", vbCritical

Exit Sub

End If

If IsNumeric(Me.TextBoxPosEntryRate.Value) = False Then

msgbox "Please Enter the Correct Sale Price", vbCritical

Exit Sub

End If

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Pos_Entry_Return")

''''''''''''Update Data

Dim lr As Integer

lr = Me.TextBoxPos_Entry_Id.Value

sh.Range("AQ" & lr + 1).Value = lr

sh.Range("AR" & lr + 1).Value = Me.TextBoxPosEntryBillPrint.Value

sh.Range("AT" & lr + 1).Value = Me.TextBoxPosEntryBarcode.Value

sh.Range("AW" & lr + 1).Value = Me.TextBoxPosEntryQty.Value

sh.Range("AY" & lr + 1).Value = Me.TextBoxPosEntryRate.Value

sh.Range("AZ" & lr + 1).Value = Me.TextBoxPosEntryDisP.Value

sh.Range("BA" & lr + 1).Value = Me.TextBoxPosEntryDiscount.Value

sh.Range("BC" & lr + 1).Value = Me.TextBoxPosEntrySalesMan.Value

''''''''''Clear Boxes

Me.TextBoxPosEntryBillPrint.Value = ""

Me.TextBoxPosEntryBarcode.Value = ""

Me.TextBoxPosEntryQty.Value = ""

Me.TextBoxPosEntryRate.Value = ""

Me.TextBoxPosEntryDisP.Value = ""

Me.TextBoxPosEntryDiscount.Value = ""

Me.TextBoxPosEntrySalesMan.Value = ""

Call Show_Data

msgbox "Product has been Updated in Product Master", vbInformation

End Sub

Supper 20-20 Classes

Saturday, 4 December 2021

MS Excel VBA Code

Micro Soft VBA Project

No comments:

Post a Comment

Seperation method of Glucose in Sugarcane using HPLC || HPLC का उपयोग करके गन्ने से ग्लूकोज को अलग करने का तरीका