populate data in userfrom from closed workbooks based on combobox

  • I have a main workbook (Main Sheet) which is having 4 Userforms (EmpEntry, VenEntry,VauEntry & Userform2) for entry in different workbooks in same folder. I am useing

    Code
    Userform2

    for selecting other userform. EmpEntry For Employee details in Test.xlsx, VenEntry for Venders detailsin Vender Master.xls and VauEntry for daily vauchar entry in VaucharTest.xlsx. All the forms are working perfectly to populate data to respected workbook, but as per as the editing is concerned i am stuck. Based on

    Code
    Combobox1.Value

    I need to populate data to the userform and after editing post it back to the respective row. If it is in same workbook i use (for Empentry)


    to select data in to userform
    and


    to update it and works fine.
    But for other closed workbooks i cant' figure it out


    Any Help would be appreciated
    Thanks in advance
    Surajit

  • Re: populate data in userfrom from closed workbooks based on combobox


    Thanks Philosophaie for your reply.
    with my very little VBA knowledge I could not use your rectification, can you help me in details(how and where to use the code) please.


    Regards
    Surajit

  • Re: populate data in userfrom from closed workbooks based on combobox


    Hi


    I did change the codes for VauEntry. Modify the code for other userforms as well.



    This goes in the userform 'VauEntry' module.


    [vb]Private Sub CmdAdd_Click()

    Dim ctl As Control
    Dim RowCount As Long
    Dim myPath As String
    Dim wb2 As Workbook


    Application.ScreenUpdating = False

    RowCount = wbkOpened.Worksheets("Database").Range("A1").CurrentRegion.Rows.Count
    With wbkOpened.Worksheets("database").Range("A1")
    .Offset(RowCount, 0).Value = Me.DTPicker1.Value
    .Offset(RowCount, 1).Value = Me.TextBox5.Value
    .Offset(RowCount, 2).Value = Me.ComboBox2.Value
    .Offset(RowCount, 3).Value = Me.ComboBox3.Value
    .Offset(RowCount, 4).Value = Me.TextBox8.Value
    .Offset(RowCount, 5).Value = Me.TextBox9.Value
    .Offset(RowCount, 6).Value = Me.TextBox4.Value
    .Offset(RowCount, 7).Value = Me.TextBox1.Value


    If OptionButton1 Then
    .Offset(RowCount, 8).Value = "Cash"
    ElseIf OptionButton2 Then
    .Offset(RowCount, 8).Value = "Cheque"
    .Offset(RowCount, 9).Value = Me.TextBox6.Value
    .Offset(RowCount, 10).Value = Me.ComboBox4.Value
    End If
    End With

    wbkOpened.Save 1
    wbkOpened.Close
    Application.ScreenUpdating = True


    Unload Me
    UserForm1.Show
    MsgBox "Data Added Successfully", vbInformation, "MassageBox"

    End Sub


    Private Sub ComboBox3_Change()

    Dim X As String
    Dim Y As String
    Dim Z As String
    X = ComboBox3.Value

    If X <> "" Then
    On Error Resume Next
    Y = Application.WorksheetFunction.VLookup(ComboBox3.Value, wbkOpened.Worksheets("Head").Range("A2:B75"), 2, False)
    TextBox8.Value = Y
    Z = Application.WorksheetFunction.VLookup(ComboBox3.Value, wbkOpened.Worksheets("Head").Range("A2:C75"), 3, False)
    TextBox9.Value = Z
    Else
    TextBox8.Value = TextBox8.Text
    End If


    End Sub


    Private Sub ComboBox5_Change()
    If Me.ComboBox5.Text = "Staf" Then
    Me.ComboBox2.RowSource = "'List'!B2:B100"
    ElseIf Me.ComboBox5.Text = "Vander" Then
    Me.ComboBox2.RowSource = "'List'!D2:D100"
    End If
    End Sub


    Private Sub CommandButton2_Click()
    wbkOpened.Activate
    End Sub


    Private Sub CommandButton3_Click()
    Application.Goto wbkActive.Worksheets(Sheet1).Range("a1")
    End Sub


    Private Sub OptionButton1_Click()

    Dim iRow As Long
    Dim ws As Worksheet
    Dim objctrl As Control

    Set ws = wbkOpened.Worksheets("Database")

    For Each objctrl In Me.Controls
    If OptionButton1.Value Then TextBox6.Visible = False
    If OptionButton1.Value Then ComboBox4.Visible = False
    Next


    End Sub


    Private Sub OptionButton2_Click()
    Dim objctrl As Control

    For Each objctrl In Me.Controls
    If OptionButton2.Value Then objctrl.Visible = True
    Next

    End Sub


    Private Sub TextBox1_Change()
    OnlyNumbers
    End Sub
    Private Sub OnlyNumbers()
    If TypeName(Me.ActiveControl) = "TextBox" Then
    With Me.ActiveControl
    If Not IsNumeric(.Value) And .Value <> vbNullString Then
    MsgBox "Sorry, only numbers allowed"
    .Value = vbNullString
    End If
    End With
    End If
    End Sub


    Private Sub TextBox4_Change()
    TextBox4.Text = VBA.StrConv(TextBox4.Text, vbProperCase)
    End Sub


    Private Sub UserForm_Activate()

    Dim ctl As Control
    Dim RowCount As Long
    Dim myPath As String
    Dim wb2 As Workbook
    Dim LastRow As Long


    Application.ScreenUpdating = False

    RowCount = wbkOpened.Worksheets("Database").Range("A1").CurrentRegion.Rows.Count

    With wbkOpened.Worksheets("Database")

    LastRow = .Range("b" & .Rows.Count).End(xlUp).Row

    'Set the min/max button setting for the form.
    AddMinMaxButtons Me.Caption, MinButton:=True, MaxButton:=True


    Me.TextBox1.Value = ""
    Me.ComboBox2.Value = ""
    Me.ComboBox3.Value = ""
    Me.ComboBox4.Value = "Bank"
    Me.TextBox4.Value = ""
    Me.TextBox6.Value = "Chqu No"
    Me.TextBox1.SetFocus

    Me.TextBox5.Value = .Range("b" & LastRow) + 1
    'Call Macro3

    'to close the workbook uncomment the following line

    wbkOpened.Close True

    End With

    End Sub


    Private Sub UserForm_Initialize()
    Me.ComboBox5.RowSource = "'List'!F2:F4"
    DTPicker1.Value = Date
    Me.ComboBox4.RowSource = "'List'!I2:I4"
    End Sub[/vb]


    this goes in a standard module


    [vb]Public wbkOpened As Workbook
    Public wbkActive As Workbook[/vb]


    Replace your commandbutton11_click event code on Userform2 with the following.


    [vb]Private Sub CommandButton11_Click()

    Dim ctl As Control
    Dim RowCount As Long
    Dim myPath As String
    Dim wb2 As Workbook

    If ComboBox11.Value = "" Then
    MsgBox "Please select a Valied Form Name"
    Exit Sub
    End If

    Set wbkActive = ThisWorkbook

    Application.ScreenUpdating = False
    Select Case ComboBox11.Value
    Case "StaffMaster"
    myPath = ActiveWorkbook.Path
    Set wbkOpened = Workbooks.Open(myPath & "\" & "Test.xlsx")
    EmpEntry.Show

    Case "Vauchar"
    myPath = ActiveWorkbook.Path
    Set wbkOpened = Workbooks.Open(myPath & "\" & "VaucharTest.xlsx")
    VauEntry.Show
    Case "VenderMaster"
    myPath = ActiveWorkbook.Path
    Set wbkOpened = Workbooks.Open(myPath & "\" & "Vender Master.xlsx")
    VenEntry.Show
    End Select
    Application.ScreenUpdating = 1

    End Sub[/vb]



    HTH

  • Re: populate data in userfrom from closed workbooks based on combobox


    Thanks krishnakumar for your help. i have tried your code. but it is giving Run time Error '380' could not set the RowSource property. invalid property value. can you see to it please. secondly if you just go through EmpEntry or VenEntry it is working fine but i need to populate entair row based on

    Code
    combobox1.value

    but failed. FOR VauEntry i have not yet created the combobox for editing row. Thank you so much for taking so mush pain to help me.
    Regards
    Surajit

  • Re: populate data in userfrom from closed workbooks based on combobox


    Quote from mammam;626345

    Thanks krishnakumar for your help. i have tried your code. but it is giving Run time Error '380' could not set the RowSource property. invalid property value. can you see to it please. secondly if you just go through EmpEntry or VenEntry it is working fine but i need to populate entair row based on

    Code
    combobox1.value

    but failed. FOR VauEntry i have not yet created the combobox for editing row. Thank you so much for taking so mush pain to help me.
    Regards
    Surajit



    Any help ?

  • Re: populate data in userfrom from closed workbooks based on combobox


    Hi,


    Do the changes.



    VauEntry userform module


    [vb]Private Sub UserForm_Initialize()

    With Me
    .ComboBox5.RowSource = vbNullString
    .ComboBox5.List = List1
    .ComboBox5.ListIndex = 0
    DTPicker1.Value = Date
    .ComboBox4.RowSource = vbNullString
    .ComboBox4.List = List2
    .ComboBox4.ListIndex = 0
    End With

    End Sub


    Private Sub ComboBox5_Change()
    If Me.ComboBox5.Text = "Staf" Then
    Me.ComboBox2.List = SList
    ElseIf Me.ComboBox5.Text = "Vander" Then
    Me.ComboBox2.List = VList
    End If
    End Sub[/vb]



    Userform2 Module


    [vb]Private Sub UserForm_Initialize()

    If wbkActive Is Nothing Then Set wbkActive = ThisWorkbook

    List1 = Range("'List'!F2:F4")
    List2 = Range("'List'!I2:I4")

    VList = Range("'List'!B2:B100")
    SList = Range("'List'!D2:D100")

    End Sub[/vb]



    In a standard module


    [vb]Public List1, List2
    Public VList, SList[/vb]


    HTH

  • Re: populate data in userfrom from closed workbooks based on combobox


    Hi


    You haven't replaced the CommandButton11_Click code.


    [vb]Private Sub CommandButton11_Click()

    Dim ctl As Control
    Dim RowCount As Long
    Dim myPath As String
    Dim wb2 As Workbook

    If ComboBox11.Value = "" Then
    MsgBox "Please select a Valied Form Name"
    Exit Sub
    End If

    Set wbkActive = ThisWorkbook

    Application.ScreenUpdating = False
    Select Case ComboBox11.Value
    Case "StaffMaster"
    myPath = ActiveWorkbook.Path
    Set wbkOpened = Workbooks.Open(myPath & "\" & "Test.xlsx")
    EmpEntry.Show

    Case "Vauchar"
    myPath = ActiveWorkbook.Path
    Set wbkOpened = Workbooks.Open(myPath & "\" & "VaucharTest.xlsx")
    VauEntry.Show
    Case "VenderMaster"
    myPath = ActiveWorkbook.Path
    Set wbkOpened = Workbooks.Open(myPath & "\" & "Vender Master.xlsx")
    VenEntry.Show
    End Select
    Application.ScreenUpdating = 1

    End Sub[/vb]


    Also


    replace these lines


    [vb] wbkOpened.Save 1
    wbkOpened.Close[/vb]


    with


    [vb]wbkOpened.Close 1[/vb]

  • Re: populate data in userfrom from closed workbooks based on combobox


    thanks krishnakumar,
    Tried your modified code, but heard luck. the Vlook up for category sub category is not working and getting error. changed file attached. On the other hand EmpEntry is working fine. but in EmpEntry i am not able to populate worksheet data to userform based on

    Code
    combobox1.Value

    for editing and post back to respected row of text.xlsx
    I am trying to use Main Sheet.xlsx as Master of all Accounts related userform. And userform will be used for entry and edit of data to respected workbooks.entry part is working fine but unable to populate data to userform for editing. Please see EmpEntry and help

  • Re: populate data in userfrom from closed workbooks based on combobox


    Hi,


    I haven't changed the code for EmpEntry and VenEntry userforms. You have to adopt the code I made for VauEntry and do the necessary changes.


    In the meantime replace these lines


    [vb] Application.ScreenUpdating = False


    myPath = ActiveWorkbook.Path


    Workbooks.Open myPath & "\" & "Test.xlsx"


    Set wb2 = ActiveWorkbook[/vb]


    with


    [vb]wbkOpened.Activate[/vb]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!