[dba-Tech] A couple of Excel questions

Thanks, Jim!

I will inspect the code tomorrow before dinner, but tonight I have to go to
an annual Christmas Eve party held by two of my oldest friends and a dozen
of so of their oldest friends. (Since we are all relatively old, this means
that the friendships go back 30 or 40 years. It's a nice way to spend
Christmas Eve.

I'll look at your code tomorrow. Meanwhile I have to prepare to go 30 miles
to the party. (Don't worry; I'm not driving. I'm way too smart for that!)

Have a nice Christmas and all the best in 2008.

Thanks,
Arthur

On 12/24/07, Jim Lawrence <accessd at shaw.ca> wrote:
>
> Hi Arthur:
>
> A couple of years ago I wrote a full excel spreadsheet application that
> allowed users to add and delete records for a company in Toronto. The
> whole
> code was written in VBA...
>
> Add or Update row:
>
> Private Function SaveResults(bolAddingRecord As Boolean) As Boolean
>
> If ValidateData() = True Then
>   ' Will a new row need to be added?
>   If bolAddingRecord = True Then
>     'Go to top row and loop until an empty space is found
>     If Not IsEmpty(ActiveCell) = True Then
>      Do  'Find first blank space
>       ActiveCell.Offset(1, 0).Select
>       Loop Until IsEmpty(ActiveCell) = True
>      End If
>
>      bolAddingRecord = False
>
>   End If
>
>   ' Write content from selected combo box and do all cell formatting here.
>   On Error resume next
>   ActiveCell.Value = cmbAccountCode.Value
>
> ...
>
> '.........................................................................
>
> Delete Row:
>
> ' Method 1: This is the simplest way to delete records but it deletes the
> entire row contents which may not be what you want.
>
> Public Function DeleteRecord()
>
> If MsgBox("Do you wish to DELETE the current Record?", vbExclamation +
> vbOKCancel, "Delete current Record") = vbOK Then
>
>   If IsEmpty(ActiveCell) = False Then
>    Selection.EntireRow.Delete
>
>    If IsEmpty(ActiveCell) = True Then
>
>     ' Variable that hold record count
>     ' lngRecords = ThisWorkbook.Worksheets(1).UsedRange.Rows.Count - 1
>     If lngRecords > 1 Then
>      lngPosition = lngPosition - 1
>      ActiveCell.Offset(-1, 0).Select
>     End If
>
>    End If
>   End If
> End If
>
> End Function
>
> '.........................................................................
> ' Method 2: Deletes a range of columns by moving the contents from the
> rows
> below up one row.
>
> Public Function DeleteRecord()
>
> If MsgBox("Do you wish to DELETE the current Record?", vbExclamation +
> vbOKCancel, "Delete current Record") = vbOK Then
>
>   If IsEmpty(ActiveCell) = False Then
>     MoveRange
>     lngRecords = lngRecords - 1
>
>     If IsEmpty(ActiveCell) = True Then
>      If lngRecords > 1 Then
>        lngPosition = lngPosition - 1
>        ActiveCell.Offset(-1, 0).Select
>      End If
>    End If
>   End If
>
>   frmEntry.lblRecordPosition.Caption = "Record " & lngPosition & " of " &
> lngRecords
>
> End If
>
> End Function
>
> Public Function MoveRange()
>   Dim lngRowPosition As Long
>   Dim lngRowCount As Long
>   Dim strCell As String
>
>   lngRowPosition = Application.ActiveCell.Row
>   lngRowCount = 0
>   strCell = "a" & lngRowPosition
>
>   Application.ScreenUpdating = False
>
>   lngRowCount = GetRecordPosition()
>
>   Range(strCell).Select
>
>   Selection.CurrentRegion.Select
>   Selection.Offset(lngRowPosition, 0).Resize(lngRowCount - lngRowPosition
> +
> 2).Select
>   Selection.Copy
>
>   Range(strCell).Select
>
>   ActiveSheet.Paste
>   Application.CutCopyMode = False
>
>   Application.ScreenUpdating = True
>
>   Range(strCell).Select
>
> End Function
>
> Public Function GetRecordPosition() As Integer
>   Dim intCounter As Integer
>
>   Range("a2").Select
>
>   intCounter = 0
>
>   Do
>     If IsEmpty(ActiveCell) = False Then
>       intCounter = intCounter + 1
>       ActiveCell.Offset(1, 0).Select
>     End If
>   Loop Until IsEmpty(ActiveCell) = True
>
>   Range("a2").Select
>
>   GetRecordPosition = intCounter
>
> End Function
>
>
> This code was specifically written for a hard coded application so the
> offsets are not using variables but I hope this helps.
>
> Jim
>
>
> -----Original Message-----
> From: dba-tech-bounces at databaseadvisors.com
> [mailto:dba-tech-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
> Sent: Monday, December 24, 2007 7:00 AM
> To: Discussion of Hardware and Software issues
> Subject: [dba-Tech] A couple of Excel questions
>
> I have a workbook containing 3 tables side by side. The range of table 1
> is
> A3:D15, of table 2 it's F3:F15, of table 3 it's K3:N15.
>
> What I need to do is lose Row 3 and then insert a new row at Row 15. But
> because the table has borders, what was row 15 orignally now has a bottom
> edge, and the new row 15 doesn't. I know how to do a selection and kill
> its
> formatting, and then do another selection and give it a bottom border, but
> that is so clumsy.
>
> First of all, what are commands to delete a row and to insert a row?
>
> Second, it happens that frequently some rows in the table have not been
> filled yet. In that case, I'd like to find the first blank row  and do the
> row insert there, thus simplifying the the formatting code
>
> Third, how to you copy just the value from a cell to another cell, without
> copying the formatting too?
>
> Thanks and Happy Holidays to all.
> Arthur
> _______________________________________________
> dba-Tech mailing list
> dba-Tech at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-tech
> Website: http://www.databaseadvisors.com
>
> _______________________________________________
> dba-Tech mailing list
> dba-Tech at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-tech
> Website: http://www.databaseadvisors.com
>