Using excel as a database : Help required...

  Gaz 25 21:20 11 Feb 2005


As part of college work, I've been asked to create a database system for a food shop using excel.. yes I know.. should be using access. However, the course requirements are that it must be done in Excel with VB used too, and to be honest - it isn't to bad using excel as a database other than it can be sluggish and hard to get working like access.

I have a problem, maybe you can help! Basically, I have got a VB GUI interface (screenshot here: click here )

Now, how do I go about getting the form to call data and the next and back buttons to work? All the data is in a sheet named: CustomerDetails

With regards,

Thanks in advance.

  VoG II 21:26 11 Feb 2005

Is this a Userform called up in VBA.

Is it used to input the data into the spreadsheet or the other way round.

Presumably the Next/Back buttons are supposed to call up the next or previous row of data.

  Gaz 25 21:36 11 Feb 2005

Yes, a useform it is.

A bit of both to be honest, it calls data, displays it, and if required you can edit it.

Edit customer button is: Editcust.Hide

And, yes, the next and back buttons do change the row. Is this possible?


Thank you for your prompt reply!

  Gaz 25 21:57 11 Feb 2005

Typo, sorry!

  VoG II 22:01 11 Feb 2005

I'm not suteif this will hep but it is a snippet of code used to read data off a userform

Sub Import()

Dim OpenBook As String, vol, Reps, Sheet As String

Dim Iset, Iday, DRange As Range, Offset As Integer

Dim OpenFile As Boolean, Rng As Range, VRng As Range

Dim nRow As Integer, nCol As Integer, iLoc As Integer

Set TestDlg = DialogSheets("TestDlg")

Set InSheet = Sheets("Input")

OpenFile = Application.Dialogs(xlDialogOpen).Show

If Not OpenFile Then Exit Sub


UserForm1.RefEdit1.Text = ""

UserForm1.RefEdit2.Text = ""


Set Rng = Range(UserForm1.RefEdit1.Text)

Set VRng = Range(UserForm1.RefEdit2.Text)

nCol = Rng.Columns.Count

nRow = Rng.Rows.Count


Your Next and Back button codes should presumably be something like this (for Next)

iRow = iRow + 1

  Gaz 25 22:06 11 Feb 2005

I'll try that now. :-)

Best regards,

  Gaz 25 22:24 11 Feb 2005

The next and back buttons don't seem to work VoG™.

Hmm, is there any other code to make it move row?

I've also tried control source and, I can't get move forward and back to work with that either.

Thanks for any help in advance.

  VoG II 22:31 11 Feb 2005

If you right click one of those buttons you should see a View Code button. If you select that it will bring up a VBA window containing some pre-defined code. You can get rid of most of that and substitute

iRow = iRow + 1

or similar.

You will need to have defined iRow as a global variable at the top of your module like:

Public iRow

  Gaz 25 22:41 11 Feb 2005

Thanks... It seems to be a little fragile, and I get a lot of complie errors. :-(

It's the way I have my code I think.

However, would it not be easier to use Control Source, because that also modifies the data in the feild.

All I need is a row down and up control for my buttons, iRow doesn't work with control source.

Thanks again for your expert advice.


  VoG II 22:48 11 Feb 2005

You would need to write the value of iRow to a worksheet (which could be hidden). Control Source would be linked to that sheet (say Sheet1!A1).

So your routine would do something like

iRow = iRow + 1

Sheets("Sheet1").Range("A1").Value = "B" & iRow

  Forum Editor 23:01 11 Feb 2005

is to watch a real expert doing what he/she does best.

I get that feeling whenever I watch VoG™ deal with an Excel problem/query, and I realise how lucky we are to have such expertise in our midst. I have worked with computers for decades, and I flatter myself I'm not too bad when it comes to some aspects of computing. Excel isn't one of my strong points however, as I freely admit. I'm happy to sit back and see VoG™ do his stuff - you couldn't have a better mentor, Gaz 25

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

HP Envy x2 review: Hands-on

How Sketch and InVision have revolutionised our design workflow

The best tech gifts for Christmas 2017

Les meilleurs jeux de société (2017)