Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 17th, 2009, 10:59 PM
Registered User
 
Join Date: Feb 2009
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Modifying Excel 2002 VBA Programmer's Reference Useforms

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
 
Old February 18th, 2009, 02:06 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

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
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)





Similar Threads
Thread Thread Starter Forum Replies Last Post
Changing Marker color in Excel 2002 vba boulder_dude2000 Excel VBA 0 February 3rd, 2009 03:20 PM
Excel 2002 VBA Programmer's Reference hawlk VB How-To 0 February 4th, 2007 04:35 PM
Suspected Error in "Excel 2002 VBA" book rstober Excel VBA 7 September 2nd, 2004 05:48 PM
A simple question about date in VBA for Excel 2002 Trancefuzion Excel VBA 2 July 29th, 2004 04:37 AM
Excel 2002 VBA Programmers Guide quinoman Wrox Book Feedback 1 June 9th, 2003 09:07 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.