Need Advice for Worksheet Design and Coding for New Project

Posted on 2012-03-15
Medium Priority
Last Modified: 2012-06-21

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

Question by:Massimo Scola
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
LVL 14

Expert Comment

by:Zack Barresse
ID: 37725747
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.

Assisted Solution

reitzen earned 1000 total points
ID: 37725817
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

LVL 14

Expert Comment

by:Zack Barresse
ID: 37726166
@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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

by:Massimo Scola
ID: 37728763
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


Expert Comment

ID: 37730149
You could use the MATCH function to get the row number before you update/delete the row in your table/data range.
LVL 14

Accepted Solution

Zack Barresse earned 1000 total points
ID: 37730236
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

Author Closing Comment

by:Massimo Scola
ID: 37736729
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


Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question