Announcement

Collapse
No announcement yet.

Retrieve and change data with VBA Userform in Excel

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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, 22:40. Reason: Replaced quote tags with code tags

  • #2
    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.

    Comment


    • #3


      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

      Comment

      Working...
      X