• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • Last Modified:

Need Advice for Worksheet Design and Coding for New Project

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
Massimo Scola
Asked:
Massimo Scola
  • 3
  • 2
  • 2
2 Solutions
 
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.
0
 
reitzenCommented:
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
 
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 :) )

Regards,
Zack Barresse
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
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?

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
 
reitzenCommented:
You could use the MATCH function to get the row number before you update/delete the row in your table/data range.
0
 
Zack BarresseCEOCommented:
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
 
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

Massimo
0
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now