+ Reply to Thread
Results 1 to 2 of 2

Here's one for the experts

  1. #1
    Registered User
    Join Date
    10-23-2006
    Posts
    84

    Here's one for the experts

    The Program listing below works fine, except that I need the Date formatted as a date and the invoice cost as a currency. If I preformat the sheet prior to running the UserForm then the usedrange sees them as filled cells and skips them, so, if I format from A2 to a100 the first entry goes in at A1 and the second at A101.

    Dim lCurrentRow As Long


    Private Sub cmdPrevious_Click()
    If lCurrentRow > 3 Then
    SaveRow
    lCurrentRow = lCurrentRow - 1
    LoadRow
    End If
    End Sub

    Private Sub cmdNext_Click()
    SaveRow
    lCurrentRow = lCurrentRow + 1
    LoadRow
    End Sub

    Private Sub cmdDelete_Click()
    Dim sMessage As String
    sMessage = "Are You Sure You Want To Delete " + txtInvNum.Text + "?"
    If MsgBox(sMessage, vbQuestion + vbYesNo, "Confirm Delete") = vbYes Then
    Rows(lCurrentRow).Delete
    LoadRow
    End If
    End Sub

    Private Sub cmdAdd_Click()
    SaveRow
    If Cells(lCurrentRow, 1).Value = "" Then
    lCurrentRow = 3
    Else
    lCurrentRow = ActiveSheet.UsedRange.Rows.Count + 1
    End If
    LoadRow
    txtDate.SetFocus
    End Sub

    Private Sub UserForm_Activate()
    lCurrentRow = 3
    LoadRow
    End Sub

    Private Sub cmdClose_Click()
    SaveRow
    Unload Me
    End Sub

    Private Sub LoadRow()

    txtDate.Text = Cells(lCurrentRow, 1).Value
    txtInvNum.Text = Cells(lCurrentRow, 2).Value
    txtInvSup.Text = Cells(lCurrentRow, 3).Value
    txtInvCost.Text = Cells(lCurrentRow, 4).Value
    End Sub

    Private Sub SaveRow()
    Cells(lCurrentRow, 1).Value = txtDate.Text
    Cells(lCurrentRow, 2).Value = txtInvNum.Text
    Cells(lCurrentRow, 3).Value = txtInvSup.Text
    Cells(lCurrentRow, 4).Value = txtInvCost.Text
    End Sub


    I ran a macro to see what was needed to format the cells from within the routine an added the this as a sub routine with the ell range modified so:-

    Sub Fomat()
    Range(Cells(lCurrentRow, 1)).Select
    Selection.NumberFormat = "d/mm/yyyy;@"
    Range(Cells(lCurrentRow, 4)).Select
    Selection.NumberFormat = "[$-809]#,##0.00;[Red][$-809]#,##0.00"
    End Sub

    It just does not work. Anyone any idea why please?

  2. #2
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628
    I saw you use, in the 'Private Sub cmdAdd_Click()' sub the code:

    lCurrentRow = ActiveSheet.UsedRange.Rows.Count + 1

    to get the first free row.

    You may try this:

    lCurrentRow = ActiveSheet.range("A3").CurrentRegion.Rows.Count + 3 'because you start from row 3

    or you can also try with this:

    lCurrentRow = ActiveSheet.Range("a65536").End(xlUp).Row + 1

    and so you can preformat your sheet.

    Regards,
    Antonio

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1