You said you can put the ActiveX button on the sheet.
to create the VBA code, right-click the button and select "View Code".It will
open the VB Editor window and create a default "click" event for the button.
Now, as for what you want the macro to DO:
so, you just want to cycle through all cells, and if the cell says
"Failed",then change it to "Passed" and get rid of the red highlight?or is the
highlighting part of a conditional format?
Start by using the macro recorder (on the Developer tab) and record a macro in
which you make the necessary changes to a single cell.
Copy these lines into your button "click" event macro.
Now, to cycle through the cells, define a "range" variable:
dim rng as Range
then, set up a for..each.. loop that cycles through your table:
For Each rng In Range("AC3:AH30")
Now, for each of these cell ranges, you only want to modify ones that contain
"Failed".
so add an "IF()) statement to check the value:
If (rng.Value = "Failed") then
put your copied commands next.Your recorded commands will have things like
"With Selected."and "Activecell."replace these with your rng variable and put a
"Next" statement to end your loop.
then add "End If" to close the IF() statement.
You should end up with something like:
Private Sub CommandButton1_Click()
Dim rng As Range
For Each rng In Range("AC3:AH30")
If (rng.Value = "Failed") Then
With rng.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
rng.FormulaR1C1 = "Passed"
End If
Next rng
End Sub
Paul-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
On Thursday, October 27, 2016 10:21 PM, Nadal Mir <nadalmi...@gmail.com>
wrote:
|
down votefavorite | My everyday works at office is to validate bunch of data in
excel, which turn Failed status to Passed. My question is how to make an active
x control button that works to change all Failed status data to Passed With
just one click. i just know to put the button but to create a VB code for it is
my big problem. thanks in advance for any help..here is example of my work. How
to make PASSED ALL button functioning
|
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
