Solved

update a button to have multiple words on it, and column with another background color

Posted on 2016-10-23
21
40 Views
Last Modified: 2016-10-24
Hello and good morning Expert Exchange users and elite helpers.

I would like to update two things for this file to have.

---Have also a button that shows the Name on the button every time i press it for "H" column
---Have Column "K","L" and "M" have a color of (Orange, Accent 1, lighter 40%) on those column

Please for more thorough information on what is it to be done for this file, please click on the following link and if there is any inconvenience or question feel free to ask. Thanks again Expert Exchange.

https://www.youtube.com/watch?v=E5dm5nw7Udw&feature=youtu.be
28977912a.xlsm
0
Comment
Question by:Omar Hernandez
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 10
21 Comments
 
LVL 47

Expert Comment

by:Martin Liss
ID: 41856116
In module9 replace the RectangleBeveled6 sub with this.
Sub RectangleBeveled6_Click()

'new
If ActiveCell.Row < 6 Then
    Exit Sub
End If

Select Case UCase(Cells(ActiveCell.Row, "H"))
    Case ""
        Cells(ActiveCell.Row, "H") = "1PRIORITY"
    Case "1PRIORITY"
        Cells(ActiveCell.Row, "H") = "2PRIORITY"
    Case "2PRIORITY"
        Cells(ActiveCell.Row, "H") = "LAST P"
    Case "LAST P"
        Cells(ActiveCell.Row, "H") = "ZDONE"
    Case "ZDONE"
        Cells(ActiveCell.Row, "H") = ""
End Select

'new
With ActiveSheet.Shapes("Bevel 10").TextFrame
    .Characters.Text = Cells(ActiveCell.Row, "H")
End With
With Range(Cells(ActiveCell.Row, "K"), Cells(ActiveCell.Row, "M")).Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent1
    .TintAndShade = 0.4
    .PatternTintAndShade = 0
End With

End Sub

Open in new window

0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 41856117
Note that the above will affect any row that you click. Do you want to restrict it to rows that have something in K through M?
0
 

Author Comment

by:Omar Hernandez
ID: 41856126
Hello there Martin Liss, i change the module but i believe it is Module11 not module9. Is it module11
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 47

Expert Comment

by:Martin Liss
ID: 41856130
Yes, you're right.
0
 

Author Comment

by:Omar Hernandez
ID: 41856140
It is working great, can we get this option to show that when i click on a row to show the actual priority that rows has.

For Example if i give a priority (last) to row 22,  Instead of it showing what was the last row given priority being (last), once i click on another row, if other row has a different priority or none have the button showing actual row priority.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 41856150
Give this a try.
28978276.xlsm
0
 

Author Comment

by:Omar Hernandez
ID: 41856158
Looks great, now what it needs is the column K, L, and m to have a different color than the column before K
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 41856167
I don't understand what you mean. When you click any cell, that row becomes yellow. If you then click the button we've been modifying, columns K:M become orange. and that's "a different color than the column before K".
0
 

Author Comment

by:Omar Hernandez
ID: 41856209
ok let me further explain, the background is white on the file,

Keep the color white for the background of column "A" through "J"

what i want it to do is, due to column "K", "M" and "L" being there for a different purpose is the following

-Have these column have the background with the color (Orange)

What i mean by saying yellow

keep the function that when i click on a row.
-Have the color yellow distinguishing the row i have selected.
0
 

Author Comment

by:Omar Hernandez
ID: 41856216
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 41856218
In the RectangleBeveled6_Click code you''ll find this:
    With Range(Cells(ActiveCell.Row, "K"), Cells(ActiveCell.Row, "M")).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.4
        .PatternTintAndShade = 0
    End With

Open in new window

Just before it or after it add this:
    With Range(Cells(ActiveCell.Row, "A"), Cells(ActiveCell.Row, "J")).Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

Open in new window

0
 

Author Comment

by:Omar Hernandez
ID: 41856243
where would i find the rectanglebeveled6

Module  or others ?
0
 

Author Comment

by:Omar Hernandez
ID: 41856244
I have found it, but i want the same color you have created to stay put on the whole column "K" through "M"


Look at this pic just like this

row.png
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 41856291
Is this what you want?

If I click on a cell in, say, row 20
I press the button
o Range K5:M105 (or whatever the last row is) changes color to orange
o Range A20:J20 turns white
o I click a cell in any other row, say, row 30
o Range A30:J30 turns yellow and range K30:M30 stays orange
0
 

Author Comment

by:Omar Hernandez
ID: 41856432
I hope this work Martin Liss

The file when i open it looks like this, without me clicking on any cell.

---Notice how the column all has a "Fill Color" of white.

The-file-when-i-open-it-looks-like-t.png
I want the file to look like this when i open it.

---Notice now how the "K", "L" and "M" has a "Fill Color" of (Orange, Accent 1, Lighter 60%

the-way-i-want-the-file-looking-like.png
I want the file to look like this when i click on it.

The-way-i-want-the-file-to-look-when.png
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 41856459
It would be a lot ester for me to understand what you want if you would describe your requirements like I did in post ID: 41856291, but take a look at this.
28978276b.xlsm
0
 

Author Comment

by:Omar Hernandez
ID: 41856467
Please check out this video it will be much clearer.

https://www.youtube.com/watch?v=yv_VIfrz7Us&feature=youtu.be
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 41857196
OK I think I understand, but I have a new question. Once columns K:M are orange, how do they get back to normal? In other words if clicking row 20 makes range K20:M20 orange (along with most of the other cells in those columns), do they ever get back to being white, and if so, how?
0
 

Author Comment

by:Omar Hernandez
ID: 41857224
Can we create a new Button to be right next to the button you have recently created.

Button ta say "ON" when having those three column in (ORANGE)

and

Button to say "OFF" when having all column in (WHITE)

button-where-it-goes.png
0
 
LVL 47

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 41857416
Is this is?
28978276c.xlsm
0
 

Author Closing Comment

by:Omar Hernandez
ID: 41857528
Thanks a mill Martin Liss, the file is working great.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question