Need Advice for Worksheet Design and Coding for New Project


I've just started a new, bigger Excel Project. As this workbook will also be used by people who are not very proficient with spreadsheets, I've decided that all data will be entered with userforms.

I have come across one big problem with the worksheet design and  its coding. To help you understand what I'm trying to do, I've created a prototype which I've attached to this posting.

A worksheet contains a name of shops. Each shop belongs to a category. The categories will be retrieved from a named range. New categories can be added and deleted.  To keep it like a database, I need to place the categories somewhere in the sheet / workbook - somewhere where it may be visible for advanced users to see the statistics.

I've created two different sheets with  ideas:
In sheet 1, I've added the categories above the table containing the shops. How would you solve the problem with adding and removing the data? Please have a look at my VBA coding.
In Sheet 2, I've added the information on the right of the table. The problem is, obviously, that if I were to make any changes, it would mess up the table design.

How would you tackle this problem? Do you have any suggestions?

Thank you for your help

Massimo ScolaAsked:
Who is Participating?
Zack BarresseConnect With a Mentor CEOCommented:
If you don't want headaches down the road, use a different worksheet.  Think of worksheets, and even Tables to a point, like database tables.  If they grow vertical, don't block the bottom.  If they grow to the right, don't block that either.  Envision where you think it's going to go, then quadruple that at the very least.  I recommend that anything dynamic have nothing below it.

As far as your code, I think you're putting the cart before the horse.  Personally, I would grab a notepad, writing utensil, and draw out what you're trying to do.  Get it right on paper, get the workflow straight in your head, then put it to paper.  Once it's there, you've got blueprints to build it on the worksheet.  Once it's on the worksheet, populate it with some data.  Once you have some data, start your coding.

To specifically answer your questions, if you know what range to use to populate your listbox, you can find what row you've referenced by the selection by looping through each item and checking it's Selected state, thus correlating your index of the listbox to your index of your worksheet range.

I'm sure you've heard the old adage, form follows function, which is good, but sometimes it's ok to smudge.  :)

Zack Barresse
Zack BarresseCEOCommented:
Hi there,

I wouldn't add any tables above data like that, unless you can safely assume (or just flat out know) the data won't grow.  Make tables to left-to-right instead of top-to-bottom (as far as laying them out).  These aren't actual Tables though, just data in cells.  Were you going to put them in an actual Table?  It wouldn't be a bad idea.  Then you could just refer to the Table and it's header in code as a named range.  Or set the Table to an object (needs to be referred to as a ListObject) in your code.  In other words, if you won't have any other Categories of shops, your table of data could potentially go anywhere.  I'm assuming this isn't the case with your "NEW" button on your userform though.

I'm a big proponent of putting list data in another location, and not on the same worksheet/area as your actual data.  I would recommend putting your lists on another worksheet and accessing from there.
reitzenConnect With a Mentor Commented:
What if you were to have the two "database tables" contained in separate worksheets?  This would eliminate any problems when adding/deleting rows.

As a suggestion, you might want to try a formula based approach for your dynamic named ranges.  This will keep the entire table contained in the named range allowing you to add/delete rows/columns at will.

Open in new window

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Zack BarresseCEOCommented:
@reitzen: If Tables were used, you can just name the range to the column of the Table, leaving out a need for complex formulas.  (I've used that approach many times prior to 2007 though :) )

Zack Barresse
Massimo ScolaAuthor Commented:
Sorry for my late reply and thanks for your feedback

I could put the names of the categories in a new worksheet but then I'd end up having x numbers of sheets. Is it very difficult to make the dynamic range grow and shrink above a table?

In order to add a new data, I use the following code:
I use End(xlDown) to find the first empty cell from the top
and I then add a new row after having inserted the data with
.Insert shift:=xlDown.

Private Sub btnSave_Click()
Dim lngRow As Long

With Sheet1
        lngRow = .Range("D1").End(xlDown).Offset(1, 0).Row
        .Cells(lngRow, 4).Value = txtName.Value
        .Cells(lngRow, 5).Formula = "=COUNTIF(ShopCategory,RC[-1])"
        .Cells(lngRow, 6).Formula = "=SUMIF(ShopCategory,RC[-2],ShopOrders)"
End With

Sheet1.Range("D1").End(xlDown).EntireRow.Insert shift:=xlDown

End Sub

Open in new window

Lets assume that I'd like to delete or edit an entry in this dynamic range with a userform:

edit/delete data
The listbox is filled with the following code:
Private Sub UserForm_Activate()
    Dim lbtarget As MSForms.ListBox

    'Set reference to the range of data to be filled
    Set rngSourceCategories = Sheet1.Range("CategoriesInformation1")

    'Fill the listbox
    Set lbtarget = Me.lstCategories
    With lbtarget
        'Determine number of columns
        .ColumnCount = 3
        'Set column widths
        .ColumnWidths = "100;50;50"
        'Insert the range of data supplied
        .List = rngSourceCategories.Cells.Value
        .ColumnHeads = True
    End With
End Sub

Open in new window

How would you code a userform which would allow me to make those changes? I need to know which row I'm in before I can delete or modify the data.

Thanks for your help

You could use the MATCH function to get the row number before you update/delete the row in your table/data range.
Massimo ScolaAuthor Commented:
Hi guys

I had a thorough look at my worksheet/project and I've decided to save some data (like the categories) in different worksheets. It is safer and easier to maintain.

Thanks for your help

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.