Button does not generate proper colors?

cloudforgiven · #1 12-21-2016, 06:27 PM

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

There is a table in my spreadsheet that has rows with different colors(2 colors gray and white that alternates in patterns), now the code above is implemented into a button and when clicked ask the user where to add a column. The column is then generated but the colors for the rows is not there? Any ideas?

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

but I am getting the same results, generated columns that have no color?

NoSparks · #2 12-21-2016, 11:20 PM

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

cloudforgiven · #3 12-22-2016, 06:24 AM

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?

NoSparks · #4 12-22-2016, 08:33 AM

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

cloudforgiven · #5 12-22-2016, 05:50 PM

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.

NoSparks · #6 12-22-2016, 07:22 PM

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

The right color is being applied but only to the starting cell of the range.

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.

cloudforgiven · #7 12-22-2016, 07:47 PM

ok and thank you for helping me this far. here is file

NoSparks · #8 12-22-2016, 08:54 PM

Is there a reason the inserted rows and columns are not, or can not, be part of the table?

cloudforgiven · #9 12-22-2016, 09:08 PM

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.

NoSparks · #10 12-22-2016, 09:48 PM

Quote:

I want the rows and columns created to be apart of the table.

Sorry, that's not what I interpreted to be wanted. Simplifies things.
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

To add a column to the table (and set its width)

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

Hope that rectifies things.
Good luck with the project.

cloudforgiven · #11 12-22-2016, 09:57 PM

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