Solved: Making a Custom Table

[bluetide76]

My goal is to have a user specify the number of rows and columns through inputboxes and then they table be created in a specified spot with that size. I know how to create a table with a range of cells like A1:D6, but how do I change this to where it specifies something like a 3x5 table. 3 rows by 5 columns. thx

[mdmackillop]

Sub table()
With Selection.Resize(3, 5).Borders
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.Weight = xlThin
End With
End Sub

[bluetide76]

That creates a border based table and doesnt actually resize my existing table. Instead of the table being defined by a range of cells how can I create the table based on rows by columns? Set up so that a user says I want a 3 row by 5 column table, and it makes it or they can say they want a 5 row by 8 column and it makes it. I'm thinking it is some adjustment for the range, but I'm too new to vba to know how it functions.

[GTO]

Greetings BlueTide,

I read your request differently than MD did, so this may be off base. Sloppy/Quick, (well... "quick" in Mark terms...) as wasn't exactly sure where you wanted the table to start etc. I chose to start upper-left corner at ActiveCell.

Hope this helps,

Mark

Sub MyMacro()
Dim RetValOne, RetValTwo, CurRow, CurCol
CurRow = ActiveCell.Row
CurCol = ActiveCell.Column
RetValOne = Application.InputBox( _
"Enter a number for how many ROWS TALL you would like your table " & _
"to be. Else - select the Cancel button to cancel..", _
"", , , , , , 1) - 1
RetValTwo = Application.InputBox( _
"Enter a number for how many COLUMNS WIDE you would like your table " & _
"to be. Else - select the Cancel button to cancel..", _
"", , , , , , 1) - 1
If RetValOne > 0 _
And RetValTwo > 0 Then
With Range(Cells(CurRow, CurCol), _
Cells(CurRow + RetValOne, CurCol + RetValTwo)).Borders

.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End If
End Sub

[mdmackillop]

That creates a border based table and doesnt actually resize my existing table. Instead of the table being defined by a range of cells how can I create the table based on rows by columns?
You say create a table, then resize an existing table. The whole spreadsheet is a table. You need to be specific in what you are after. I've got no idea what you mean.

[bluetide76]

Sorry for my lack of clarity. What I am ultimately trying to do is create a table based on an input of certain variables from the user. In my case we can call these variables months and years. Based on their input I want to create a regular table starting at a specific cell and have the number of rows equal to years input and number of columns equal to months. My hesitation is that I want it to be a regular table instead of a border highlighter. Each time the user puts a different input after clicking the start button I want to make that new table and replace the old one. I'm not sure if this is impossible or not.

So say you enter 3 years and 4 months then it would create that 3 rows by 4 columns table. Then if they were to go back to clicking the start again it would ask for new inputs, and adjust the table to that new size or just delete and replace with a new one.

[bluetide76]

Sub Table()
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$B$14:$E$19"), , xlNo).Name = _
"Table18"
Range("Table18[#All]").Select
ActiveSheet.ListObjects("Table18").ShowHeaders = False
End Sub

This is the recorded basic breakdown to making a table which I know. I want that range "Range("$B$14:$E$19")" to not specify cells but instead be set to my variables for the number of rows and columns.

[GTO]

For me, using XL2000. I don't find a .ListObject

so either a newer version, or, by chance, could this be from Word?

Was too curious not to ask,

Mark

[bluetide76]

Excel 2007

[GTO]

Well my bad then, and thank you. Have to go return a rental, so a good day to you and MD.

Hope you get a solution,

Mark

[GTO]

Hey there,

I checked back and see you're still on. Though I hesitate to "drive blind" with code I can't check/run - if its just referring to the range that's a hold-up, you could try this. If it works, great - if it don't, I cannot debug of course.

Mark

Sub Table()
Dim _
lngRows_Desired As Long, _
lngCols_Desired As Long, _
lngCurRow As Long, _
lngCurCol As Long
lngCurRow = ActiveCell.Row: lngCurCol = ActiveCell.Column

lngRows_Desired = Application.InputBox( _
"Enter a number for how many ROWS TALL you would like your table " & _
"to be. Else - select the Cancel button to cancel..", _
"", , , , , , 1) - 1

lngCols_Desired = Application.InputBox( _
"Enter a number for how many COLUMNS WIDE you would like your table " & _
"to be. Else - select the Cancel button to cancel..", _
"", , , , , , 1) - 1

If lngRows_Desired > 0 _
And lngCols_Desired > 0 Then
ActiveSheet.ListObjects.Add(xlSrcRange, _
Range(Cells(lngCurRow, lngCurCol), _
Cells(lngCurRow + lngRows_Desired, lngCurCol + lngCols_Desired)), , xlNo) _
.Name = "Table18"
Range("Table18[#All]").Select
ActiveSheet.ListObjects("Table18").ShowHeaders = False
End If
End Sub

[bluetide76]

I think that did it! I can manipulate the code to have the table start where I need it and then link the variable inputs to those ranges. Thanks for your help. I was just unclear about how the adjustment of range worked when you werent referring to a cell specified range and instead to a value of rows and columns.

[mdmackillop]

Based on GTO's solution, this should work to resize existing table containing active cell or otherwise add a new table. Interesting little project.

Option Explicit
Sub Table()
Dim lngRows_Desired As Long
Dim lngCols_Desired As Long
Dim lngCurRow As Long
Dim lngCurCol As Long
Dim Size As Variant
Dim c As Range
Dim LO As ListObject
Dim Tbl As String
Dim AddNew As Boolean
Dim Mx As Long, Cnt As Long

AddNew = True
Tbl = "Table"
lngCurRow = ActiveCell.Row: lngCurCol = ActiveCell.Column
Size = Application.InputBox( _
"Enter a COLUMNS x ROWS you would like your table " & _
"to be. Else - select the Cancel button to cancel..", , "8 x 5")
lngCols_Desired = Trim(Split(Size, "x")(0))
lngRows_Desired = Trim(Split(Size, "x")(1))

Mx = 0
For Each LO In ActiveSheet.ListObjects
If Left(LO.Name, 5) = "Table" Then
Cnt = Right(LO.Name, Len(LO.Name) - 5)
If Cnt > Mx Then Mx = Cnt
End If

Set c = Intersect(ActiveCell, LO.Range)
If Not c Is Nothing Then
LO.Resize LO.Range.Resize(lngRows_Desired + 1, lngCols_Desired)
AddNew = False
Exit For
End If
Next

If AddNew = True Then
If lngRows_Desired > 0 _
And lngCols_Desired > 0 Then
Mx = Mx + 1
ActiveSheet.ListObjects.Add(xlSrcRange, _
Range(Cells(lngCurRow, lngCurCol), _
Cells(lngCurRow + lngRows_Desired - 1, lngCurCol + lngCols_Desired - 1)), , xlNo) _
.Name = Tbl & Mx
End If
End If
End Sub

[bluetide76]

Sort of a linked question to this. With dropdown boxes for giving multiple choices. Is there a way to increment the .linkedcell ? I have a for loop that makes the number of dropdown boxes equal to the number of years but they all link to one specific cell and arent separate where the user can choose different options. .LinkedCell = "$N$1+(1*times)" I tried something like this where "times" is my increment from my loop. For times = 1 to years. Is there a way to have the linkedcell increment like that? Basically I want it to make "years" number of dropdown boxes with the same range but link them to incrementing cells so that when the user picks one of the options it gives me a different number choice.

[mdmackillop]

Too complex for my simple mind. Can you post a sample?

[bluetide76]

For Times = 1 To years
i = 57.81 * years
j = 15.25 * (Times + 1)
ActiveSheet.DropDowns.Add(116 + i, 90 + j, 57, 15).Select
With Selection
.ListFillRange = "$M$4:$M$6"
.LinkedCell = ("$L$1")
.DropDownLines = 3
.Display3DShading = True
End With
Next Times

When I run this is puts my dropdowns in the right slots that I need to subject to the years value which comes from the earlier input we talked about. But the problem with this is since .linkedcell is just fixed to one cell "L1" it makes every dropdownbox change to whatever the first is selected to. So if the user picks 2004 for the first dropdown then the rest all follow. I was hoping to have an incrementing linkedcell so that when the loop runs making each dropdown, that it also makes a separate linked cell each time. Any Idea how to adjust the above to do that?

[bluetide76]

Anyone know how to have the .linkedcell have an incrementing range? Where each time it makes the dropdown box in the loop it places the linkedcell as L1 then L2 then L3 and so on?