How’s going you guys? I’ve a question about something I’ve created on Excel. The structure I’ve made works, but it’s not efficient, since it take a couple of minutes to made and I need to replicate it, like, a hundred of times. So my goal here is to create exactly the same outputs that I’ve already have, but with a more efficient way to made it. All the process it’s pretty simple so I guess it has a alternative way to do it. I’ll post some images so you guys can understand better what I’m talking about.
I've created this structure:
All of the three buttons will always be underneath a row of merge cells and the content in the cell L5 will always be placed in the same row as the merge cells, but three columns to the right. This will always happen like that. Now let’s explain what each of these buttons does:
Button check >>> This button will validated the information that has in the cells D5:I5. The information will be validated through condittonal formatting. It will compare the value of the cells D5:I5 and if it matchs with the value of the cell L5, the cells D5:I5 will be painted in green. If the value of D5:I5 doesn’t matchs with the value of L5, it will be painted in red. The image above is exactly the final output, after i’ve clicked on the button check. Since the value of D5:I5 and L5 matchs, then D5:I5 will be painted in green.
Button delete >>> It will delete every information that D5:I5 has. But not only that, it will also remove every conditional formatting that D5:I5, making it a empty merged cells. Here’s a image of what happened when button delete was pressed:
Button paste >>> It will paste the content of the cell L5 into the cells D5:I5. Again, if cells D5:I5 has a previously conditional formatting, it will also remove it. So, basically, we will have this:
Now, I will post the code that I’m using in each of these buttons:
Button check:
Application.ScreenUpdating = False
Range("D5:I5").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With
Range("D5:I5").Select
Application.CutCopyMode = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=$L$5"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.399945066682943
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("A1").Select
Application.ScreenUpdating = True
Button paste:
Application.ScreenUpdating = False
Range("L5").Select
ActiveCell.FormulaR1C1 = "Information here"
Range("D5:I5").Select
ActiveCell.FormulaR1C1 = "Information here"
Range("D5:I5").Select
Selection.FormatConditions.Delete
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("A1").Select
Application.ScreenUpdating = True
Button delete:
Application.ScreenUpdating = False
Range("D5:I5").Select
Selection.FormatConditions.Delete
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.ClearContents
Range("E9").Select
Range("A1").Select
Application.ScreenUpdating = True
And that’s pretty much what I’ve been doing. But that structure it’s not efficient, cause I need to replicate it hundreds of times, in differents parts of my worksheet.
So what I thought was:
Since the three buttons will always be underneath merged cells, I want to create a generic structure that will recognize the place where this buttons are placed in worksheets. That means, whenever button delete is placed into a worksheets, it will automatically recognize that has to delete and remove the conditional formatting of the exactly merged cells that is directly above the button delete.
The same thing will work it with button paste: it will always paste in the directly above merged cells, the content that is located 3 columns to the left and in the same row of the merged cells (in this case, the content is information here, located on the cell L5).
And finally, the same kind of strutcture will work with button check, always checking the exactly merge cells where the button check is placed, with the content of the cell that it’s placed directly 3 rows by the side of the merged cells.
This structute is not efficient by the way I’m doing things, cause imagine a scenario like this:
I’m going to have to manually change all these buttons, so that it matchs to the logic of the codes that I’ve posted here. Now imagine have to do it a hundred of times, it’s just pretty exhausting thing to do.
Hope I’ve made myself clear in the explanation and I’m sorry if the post gets a little too big to read it, but I tried to explain with as much detailed possible so that it will not have any questions about what I’m trying to do.
Hope you guys can help me with this and if there’s another alternative solution that the one I presented, I’m glad to hear. Thank you all!!
You can use Application.Caller to determine the physical location of any Form Control button. For example, if you assign the following macro to each button on your spreadsheet:
Sub Button_Click()
Dim btn As Shape
Set btn = ActiveSheet.Shapes(Application.Caller)
MsgBox "The button is located at: " & btn.TopLeftCell.Address
End Sub
This macro will return the address of the clicked button. You can use this information to determine the row and column of the clicked button and utilize these variables in your formatting code.