Loading
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
    Posts
    2

    Retrieve and change data with VBA Userform in Excel

    Hi,

    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.


    Code:
    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
    Else
    Me.chkVisa.Value = False
    End If
     
    If Cells(RW, 22).Value = "yes" Then
    Me.chkFFnumber.Value = True
    Else
    Me.chkFFnumber.Value = False
    End If
     
    If Cells(RW, 25).Value = "yes" Then
    Me.chkAbsence.Value = True
    Else
    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
    Else
    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"
    Else
    Cells(RW, 8).Value = "no"
    End If
     
    If chkFFnumber.Value Then
    Cells(RW, 21).Value = "yes"
    Else
    Cells(RW, 21).Value = "no"
    End If
    If chkAbsence.Value Then
    Cells(RW, 24).Value = "yes"
    Else
    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 23:40. Reason: Replaced quote tags with code tags

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    9th September 2009
    Posts
    1,599

    Re: Retrieve and change data with VBA Userform in Excel

    Jolanda,

    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
    Posts
    2

    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.

    question.zip

    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, 13:33
  2. retrieve MS access data into Excel
    By rmLaurel in forum EXCEL HELP
    Replies: 2
    Last Post: June 22nd, 2004, 22:37
  3. How to retrieve data from Excel by using VBA
    By yaya1899 in forum EXCEL HELP
    Replies: 9
    Last Post: June 4th, 2004, 17:11
  4. Retrieve data from Access-database to Excel with ADO
    By XL-Dennis in forum Advanced Excel Integration
    Replies: 0
    Last Post: April 20th, 2004, 03:12
  5. retrieve data from Excel
    By philip in forum EXCEL HELP
    Replies: 2
    Last Post: August 25th, 2003, 17:28

Bookmarks

Posting Permissions

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