Modifying Excel 2002 VBA Programmer's Reference Useforms

aziztt · February 17th, 2009, 10:59 PM

Hi, was hoping you can help me, after reading the book, it gives a good example using excel as a database using ranges but the book is good as explaining to do one database. Was hoping if it can be easily modified to change to a another database quickly

For example the following code adds a single row in database1.

Code:

Option Explicit
Private Data As Variant
Private RangeData As Range

Private Sub CommandButton1_Click()
  'Add new record at bottom of Database1
  Dim RowCount As Integer

  With Range("Database1")
    'Add extra row to name Database1
    RowCount = .Rows.Count + 1
    .Resize(RowCount).Name = "Database1"
  End With
  
     'Copy values from Personal controls to Data array
   Data(1, 1) = TextBox1.Value
   Data(1, 2) = TextBox2.Value
   Data(1, 3) = TextBox3.Value
   
   'Assign Data array values to current record in Database1
   RangeData.Value = Data
   
End Sub

Is there a way putting the code into a function to call the code for another database? I have eight different databases so reusing the code will be very helpful

Possible solution example:

Code:

Private Sub CommandButton1_Click()
InPut(Database1)
End Sub

Private Sub CommandButton2_Click()
InPut(Database2)
End Sub

Private Sub CommandButton3_Click()
InPut(Database3)
End Sub

Sub Input(x)
'code
End Sub

Shasur · February 18th, 2009, 02:06 AM

You have almost done it. Can you try the following:

Code:

Private Sub CommandButton1_Click()
   Call InPutData("Database1")
End Sub
Private Sub CommandButton2_Click()
Call InPutData("Database2")
End Sub
Private Sub CommandButton3_Click()
Call InPutData("Database3")
End Sub
Sub InPutData(ByVal sDBName As String)
  Dim RowCount As Integer
  With Range(sDBName)
    'Add extra row to name Database1
    RowCount = .Rows.Count + 1
    .Resize(RowCount).name = sDBName
  End With
  
     'Copy values from Personal controls to Data array
   Data(1, 1) = TextBox1.Value
   Data(1, 2) = TextBox2.Value
   Data(1, 3) = TextBox3.Value
   
   'Assign Data array values to current record in Database1
   RangeData.Value = Data
'code
End Sub
'

[/CODE]

Cheers
Shasur