Using excel as a database : Help required...

  Gaz 25 21:20 11 Feb 05


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 05

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 05

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 05

Typo, sorry!

  VoG II 22:01 11 Feb 05

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 05

I'll try that now. :-)

Best regards,

  Gaz 25 22:24 11 Feb 05

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 05

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 05

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 05

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 05

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.

Microsoft Surface Studio PC release date, pricing, design, features and spec: Microsoft showcases…

1995-2015: How technology has changed the world in 20 years

This amazing app turns your iPad Pro into a Cintiq

Apple Q4 2016 financial results | Apple earnings report: iPhone, iPad and Mac sales down, profits d6…