Solved

Need Advice for Worksheet Design and Coding for New Project

Posted on 2012-03-15
7
202 Views
Last Modified: 2012-06-21
Hi

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
NewProject.xlsm
0
Comment
Question by:mscola
  • 3
  • 2
  • 2
7 Comments
 
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.
0
 
LVL 6

Assisted Solution

by:reitzen
reitzen earned 250 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.
=OFFSET(Sheet3!$A$1,0,0,COUNTA(Sheet3!$A:$A),COUNTA(Sheet3!$1:$1))

Open in new window

0
 
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 :) )

Regards,
Zack Barresse
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:mscola
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?

screenshot
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

Massimo
NewProject.xlsm
0
 
LVL 6

Expert Comment

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

Accepted Solution

by:
Zack Barresse earned 250 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.  :)

Regards,
Zack Barresse
0
 

Author Closing Comment

by:mscola
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

Massimo
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

708 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now