Rocky Smolin at Beach Access Software
rockysmolin at bchacc.com
Mon Dec 24 19:37:11 CST 2007
"Don't worry; I'm not driving."
Sled dogs?
Rocky
-----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 1:48 PM
To: Discussion of Hardware and Software issues
Subject: Re: [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
>
_______________________________________________
dba-Tech mailing list
dba-Tech at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-tech
Website: http://www.databaseadvisors.com
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.17.7/1194 - Release Date: 12/23/2007
5:27 PM