#1
|
|||
|
|||
Button does not generate proper colors?
Hi here is the code I've done so far:
Code:
Dim colIndex As Variant colIndex = Application.InputBox("Enter a column that you want to add: ", "What column?") If colIndex = "" Then Exit Sub With ThisWorkbook.Sheets("Sheet1").Columns(colIndex) '<--| reference column you want to insert .Insert shift:=xlRight .Offset(, -2).Copy '<--| copy the column two columns to the left of the referenced one .Offset(, -1).PasteSpecial xlPasteFormats '<--| paste formats to the new column Application.CutCopyMode = False End With here is what it looks likes: http://imgur.com/a/m04dt Notice the I column? All the way yo the right, with the the correct length and width but no colors. Maybe someone can try out the code and see what it does? The table sizes keeps changing so I don't think I can put a range in since I will have to manually change it once the table size changes? I've tried implementing Code:
.Offset(,-1).ColorIndex = .Offset(,-3).ColorIndex |
#2
|
|||
|
|||
The little black marker in the lower right corner of cell H whatever row that is indicates column I is not part of the table.
Try this Code:
Sub CloudForgiven() Dim colIndex As Variant Dim StrtRow As Long, EndRow As Long, i As Long Dim oLo As ListObject colIndex = Application.InputBox("Enter a column that you want to add: ", "What column?") If colIndex = "" Then Exit Sub With ThisWorkbook.Sheets("Sheet1") .Columns(colIndex).Insert shift:=xlRight '<--| reference column you want to insert 'sheet row numbers from table rows Set oLo = .ListObjects(1) '<~~ first table on sheet With oLo StrtRow = .ListRows(1).Range.Row EndRow = .ListRows.Count + StrtRow - 1 End With For i = StrtRow To EndRow .Cells(i, colIndex).Interior.Color = .Cells(i, 1).DisplayFormat.Interior.Color Next i End With End Sub |
#3
|
|||
|
|||
Awesome it works, I am sorry for giving you more work but I also have a button that generates rows that is having the same issue of generating the rows but not the proper cell colors, can you take a look at it? here is the code:
Code:
Dim varUserInput As Variant varUserInput = InputBox("Enter The Last Row Number In The Table:", _ "What Row?") If varUserInput = "" Then Exit Sub Dim RowNum RowNum = varUserInput Rows(RowNum & ":" & RowNum).Insert shift:=xlDown Rows(RowNum - 1 & ":" & RowNum - 1).Copy Range("A" & RowNum) Range(RowNum & ":" & RowNum).ClearContents there are rows but no color? Last edited by cloudforgiven; 12-22-2016 at 09:59 PM. |
#4
|
|||
|
|||
Did you have a look at my reply to your question here?
You can calculate the last row of the table, the input box is not necessary. It only gives the user a chance to increase the difficulty for you to program against them screwing things up. (Try entering the wrong number, a decimal number or the word "hello") What is the goal here? It appears you want the added column and row to appear to be part of the table without being part of the table. What are you actually trying to do? and why? If the column you're adding is always immediately right of the table, like the row being immediately below the table, no input boxes are required. Code:
Sub Add_Row_and_Column() Dim oLo As ListObject Dim StrtRow As Long, EndRow As Long Dim StrtCol As Long, EndCol As Long Dim i As Integer With ThisWorkbook.Sheets("Sheet1") Set oLo = .ListObjects(1) '<~~ first table on sheet 'establish table location With oLo StrtRow = .ListRows(1).Range.Row EndRow = .ListRows.Count + StrtRow - 1 StrtCol = .ListColumns(1).Range.Row EndCol = .ListColumns.Count + StrtCol - 1 End With 'add column on right of table .Columns(EndCol + 1).Insert For i = StrtRow To EndRow .Cells(i, EndCol + 1).Interior.Color = .Cells(i, StrtCol).DisplayFormat.Interior.Color Next i 'add row below table .Rows(EndRow + 1).Insert .Range(Cells(EndRow + 1, StrtCol), Cells(EndRow + 1, EndCol + 1)).Interior.Color = _ .Cells(EndRow - 1, StrtCol).DisplayFormat.Interior.Color End With End Sub Last edited by NoSparks; 12-22-2016 at 11:04 AM. Reason: added code |
#5
|
|||
|
|||
I am trying to just generate 1 column and 1 row at the end of a table at the click of a button(since the people using the spreadsheet are computer illiterate). your code does this meaning I can implement it into one button however, for some reason since the table alternates colors meaning one row gray, the other light gray, the code adds the correct column with the correct color, but the row it generates always has the same color
FYI the text is just there for testing. Notice only column A is the only column that alternates while B21-B26, all the way to the rows L21-L26 and so on and so forth do not? I need the Generated rows color to alternate just like the previous rows. Dont worry about the columns because those work its just the rows. And thank you for helping me this far because you eliminated the need for having 2 buttons and even a user input. Last edited by cloudforgiven; 12-22-2016 at 09:58 PM. |
#6
|
|||
|
|||
Looks to me like you may have missed the red part of this line
Code:
.Range(Cells(EndRow + 1, StrtCol), Cells(EndRow + 1, EndCol + 1)).Interior.Color = _ .Cells(EndRow - 1, StrtCol).DisplayFormat.Interior.Color It would be far easier to be sure if you would post the sheet with the code rather than a picture of the sheet and no code. (This isn't MrE. Excel files can be posted here.) On second thought that's not right. The new row being added is not always immediately below "the table". I'll have to adjust for that.... I'll be back shortly. Last edited by NoSparks; 12-22-2016 at 07:29 PM. Reason: second thoughts |
#7
|
|||
|
|||
ok and thank you for helping me this far. here is file
Last edited by cloudforgiven; 12-22-2016 at 09:58 PM. |
#8
|
|||
|
|||
Is there a reason the inserted rows and columns are not, or can not, be part of the table?
|
#9
|
|||
|
|||
It doesn't matter weather they are or not, as long as when I go to print the table the added rows an columns look like they are part of the table its fine. I cant use a macro because the table is dynamic here is what I mean.
Ok so I have a assignment from work where I gotta make a dynamic table because the table will be used by all kinds of people for the next few years. It will expand in size and to make it easier, I decided to use a button to add rows and columns. So whatever is the last row/column the button will continue from that and add more columns an rows 1 at a time. Now ive done this both with your code an my OP, but since the table has rows with alternating colors in a pattern, it doesn't fully copy the colors. In my OP the code generates the row, but only fills them with color once you input text in the cell. This can be misleading to other people who might think the button is not working when in fact it is, because the cells get generated but there are no cell colors UNLESS they type text. Your code does this but not really getting the proper colors for the ROWS. |
#10
|
|||
|
|||
Quote:
To add a row to the table (and set its height) Code:
Private Sub CommandButton1_Click() ' add row to table Dim oLo As ListObject Set oLo = ActiveSheet.ListObjects(1) 'first table on sheet With oLo .ListRows.Add AlwaysInsert:=True .Range.Rows(.Range.Rows.Count).RowHeight = 30 End With End Sub Code:
Private Sub CommandButton2_Click() ' add column to table Dim oLo As ListObject Set oLo = ActiveSheet.ListObjects(1) 'first table on sheet With oLo .ListColumns.Add .ListColumns(.ListColumns.Count).Range.ColumnWidth = 24 End With End Sub Good luck with the project. |
#11
|
|||
|
|||
omg dude it works, both of them. Thank you.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
proper | snoforlife | Excel Programming | 0 | 01-26-2016 02:16 PM |
Hyperlinks Not Linking to Proper Sections of Doc | MoiraB | Word | 2 | 08-26-2015 07:13 PM |
Proper Text Format | sufian,naeem | Excel | 1 | 05-05-2014 05:59 AM |
Unable to change font colors from theme colors | choy | Word | 3 | 08-01-2012 09:12 PM |
Proper Excel Table of Contents (ToC) | judicial85 | Excel | 1 | 10-23-2011 01:35 PM |