Solved

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

Posted on 2016-10-23
21
29 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
  • 11
  • 10
21 Comments
 
LVL 45

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 45

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
 
LVL 45

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 45

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 45

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 45

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 45

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 45

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 45

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 45

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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
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…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now