Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Results 1 to 3 of 3

Thread: Retrieve and change data with VBA Userform in Excel

  1. #1
    Join Date
    24th September 2010

    Retrieve and change data with VBA Userform in Excel


    I have been searching throughout the internet to figure out how I can resolve my 'problem'. I build a database in Excel. With use of a userform one can add a record (without unique field). Now I want to be able to click on a cell, click on button 'change booking' and retrieve the information in the userform for updating. The retrieving worked. But how can I overwrite the existing row with the changed data? I have to mention, that my userform has 2 multipage pages. Only one of these mutipages is to be changed.

    This is what I have so far.

    Private Sub UserForm_Click() 
        Me.MultiPage1.Value = p 
    End Sub 
    Private Sub UserForm_Initialize() 
        Dim RW As Long 
        RW = ActiveCell.Row 
        Me.txtDate.Value = Format(Cells(RW, 1), "dd-mmm-yy") 
        Me.cboRequestor.Value = Cells(RW, 3) 
        Me.txtName.Value = Cells(RW, 4) 
        Me.txtProject.Value = Cells(RW, 5) 
        Me.txtFrom.Value = Cells(RW, 6) 
        Me.txtTo.Value = Cells(RW, 7) 
        Me.cboClass.Value = Cells(RW, 10) 
        Me.cboKind.Value = Cells(RW, 11) 
        Me.txtAlso.Value = Cells(RW, 8) 
        Me.txtDeparture.Value = Format(Cells(RW, 12), "dd-mmm-yy") 
        Me.txtReturn.Value = Format(Cells(RW, 13), "dd-mmm-yy") 
        Me.txtRetLast.Value = Format(Cells(RW, 14), "dd-mmm-yy") 
        Me.txtPrice.Value = Cells(RW, 16) 
        Me.txtChange.Value = Cells(RW, 17) 
        Me.txtCancellation.Value = Cells(RW, 18) 
        Me.txtResCosts.Value = Cells(RW, 24) 
        If Cells(RW, 9).Value = "yes" Then 
            Me.chkVisa.Value = True 
            Me.chkVisa.Value = False 
        End If 
        If Cells(RW, 22).Value = "yes" Then 
            Me.chkFFnumber.Value = True 
            Me.chkFFnumber.Value = False 
        End If 
        If Cells(RW, 25).Value = "yes" Then 
            Me.chkAbsence.Value = True 
            Me.chkAbsence.Value = False 
        End If 
    End Sub 
    Private Sub cmdSubmitFlight_Click() 
        Dim RW As Object 
        Set ActiveCell.Row = RW 
        Cells(RW, 0).Value = txtDate.Text 
        Cells(RW, 2).Value = cboRequestor.Text 
        Cells(RW, 3).Value = txtName.Text 
        Cells(RW, 4).Value = txtProject.Text 
        Cells(RW, 5).Value = txtFrom.Text 
        Cells(RW, 6).Value = txtTo.Text 
        Cells(RW, 7).Value = txtAlso.Text 
        Cells(RW, 9).Value = cboClass.Text 
        Cells(RW, 10).Value = cboKind.Text 
        Cells(RW, 11).Value = txtDeparture.Text 
        Cells(RW, 12).Value = txtReturn.Text 
        Cells(RW, 13).Value = txtRetLast.Text 
        Cells(RW, 15).Value = txtPrice.Text 
        Cells(RW, 16).Value = txtChange.Text 
        Cells(RW, 17).Value = txtCancellation.Text 
        Cells(RW, 23).Value = txtResCosts.Text 
        If txtOption.Text = "" Then 
            Cells(RW, 14).Value = cboBooking.Text 
            Cells(RW, 14).Value = txtOption.Text 
        End If 
        If chkWoaBCD.Value Then 
            Cells(RW, 15).Value = "woa BCD" 
        End If 
        If chkWoaTraveller.Value Then 
            Cells(RW, 15).Value = "woa " + txtName.Text 
        End If 
        If chkVisa.Value Then 
            Cells(RW, 8).Value = "yes" 
            Cells(RW, 8).Value = "no" 
        End If 
        If chkFFnumber.Value Then 
            Cells(RW, 21).Value = "yes" 
            Cells(RW, 21).Value = "no" 
        End If 
        If chkAbsence.Value Then 
            Cells(RW, 24).Value = "yes" 
            Cells(RW, 24).Value = "no" 
        End If 
        MsgBox "One record changed" 
        Unload Me 
    End Sub 
    Could anyone help me out?
    Last edited by Rob Xaos; September 27th, 2010 at 22:40. Reason: Replaced quote tags with code tags

    Excel Video Tutorials / Excel Dashboards Reports

  2. #2
    Join Date
    9th September 2009

    Re: Retrieve and change data with VBA Userform in Excel


    Welcome to Ozgrid. Please note that VBA code should use [CODE][/CODE] tags not [QUOTE][/QUOTE] tags. I have replaced them for you this time.

    It would probably help to solve your problem if you could upload a version of your Workbook (with confidential data removed if necessary). To do that use Edit Post > Go Advanced > Manage Attachments on your post above. Your Workbook needs to be < 105KB but you can zip if necessary.

  3. #3
    Join Date
    24th September 2010

    Re: Retrieve and change data with VBA Userform in Excel

    Hi, please find attached the file so far. I am struggling on and off and am far from ready, but this is where it is now.


    Excel Video Tutorials / Excel Dashboards Reports

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. SQL to retrieve Data from Excel.
    By rajagopalanta in forum Excel and/or SQL Help
    Replies: 2
    Last Post: July 16th, 2004, 12:33
  2. retrieve MS access data into Excel
    By rmLaurel in forum Excel General
    Replies: 2
    Last Post: June 22nd, 2004, 21:37
  3. How to retrieve data from Excel by using VBA
    By yaya1899 in forum Excel General
    Replies: 9
    Last Post: June 4th, 2004, 16:11
  4. retrieve data from Excel
    By philip in forum Excel General
    Replies: 2
    Last Post: August 25th, 2003, 16:28


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts