Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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