On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Adjustment to Macro when Clearing a Case
Posted on 2014-12-13
I have a great piece of work that Glenn Ray (EE) did for me to help me with a change to a fairly advanced macro. I need one additional capability built into the macro on the attached WB.
When you open it you will see a set of rows with the ability to click on the black box (E8......First Tab) and a checkmark will appear. All selections will show up on the following tab automatically as you select rows. What Glenn did for me is give me the ability to reverse the selection. In other words, if I de-selected a row by clicking on a box already checked, it would remove it from the next tab.
What I need help with is if a selection is removed, the other selections need to move up (so there are no gaps) and the 3 columns on the second tab are cleared/erased if a particular row on the first tab is deselected. -- That's it!
Your assistance is appreciated and "thank you" in advance.
B.
Copy-of-EE-Fix-Check-Marks-v4.xlsm
When you open it you will see a set of rows with the ability to click on the black box (E8......First Tab) and a checkmark will appear. All selections will show up on the following tab automatically as you select rows. What Glenn did for me is give me the ability to reverse the selection. In other words, if I de-selected a row by clicking on a box already checked, it would remove it from the next tab.
What I need help with is if a selection is removed, the other selections need to move up (so there are no gaps) and the 3 columns on the second tab are cleared/erased if a particular row on the first tab is deselected. -- That's it!
Your assistance is appreciated and "thank you" in advance.
B.
Copy-of-EE-Fix-Check-Marks-v4.xlsm
[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
3-
2
5 Comments
Active 1 day ago
the issue is that the 'UseCase' cells on Use_Case_Priority are references to Validation_DB.
yet the Factor Cells are hard coded. I will see if amending the 'sumup' routine is possible
yet the Factor Cells are hard coded. I will see if amending the 'sumup' routine is possible
Active 1 day ago
try adding
Copy-of-EE-Fix-Check-Marks-v5.xlsm
'now fix the Use_Priority sheet
Dim ws As Worksheet, wscell As Range
Set ws = ActiveWorkbook.Sheets("Use_Case_Priority")
For Each wscell In ws.Range("B5..B10")
If wscell.Value = "" Then
'its empty so empty cells to right
wscell.Offset(0, 1).Resize(1, 3).ClearContents
End If
Next wscell
Copy-of-EE-Fix-Check-Marks-v5.xlsm
Active 2 days ago
Like this? I tried this but couldn't get it to work.............
Sub sumup()
Const Input_Range = 5
'3
Const Output_Range = 800
'17
Const Output_CS = 7
'18
Const Output_CE = 1
Const Input_CM = 5
Dim O_R As Long
'Output Row
Dim I_R As Long
'Input Row
Dim Total As Double
'Total
'**** Clear the previous output ****
With Worksheets("Validation_DB"
.Range("G800:G812") = ""
End With
O_R = Cells(Rows.Count, Output_CS).End(xlUp).Row
If O_R >= Output_Range Then
'Column and Row Output
Range(Cells(Output_Range, Output_CS), Cells(O_R, Output_CE)).ClearContents
End If
'Column Explanation
O_R = Output_Range
'Where the checkmark is
I_R = Cells(Rows.Count, Input_CM).End(xlUp).Row
If I_R >= Input_Range Then
'Input row
For I = Input_Range To I_R
With Cells(I, Input_CM)
If .Value = "P" Then
'Output results here
Sheets("Validation_DB").Ce
Sheets("Validation_DB").Ce
Sheets("Validation_DB").Ce
"_(""$""* #,##0_);_(""$""* (#,##0);_(""$""* ""-""??_);_(@_)"
If Not IsError(.Offset(0, 5).Value) Then Total = Total + .Offset(0, 5).Value
O_R = O_R + 1
End If
End With
Next
Cells(Output_Range - 1, Output_CS).Value = "Total"
With Cells(Output_Range - 1, Output_CE)
.FormulaR1C1 = "=SUM(R[1]C:R[" & O_R & "]C)"
.NumberFormatLocal = _
"_(""$""* #,##0_);_(""$""* (#,##0);_(""$""* ""-""??_);_(@_)"
End With
End If
'now fix the Use_Priority sheet
Dim ws As Worksheet, wscell As Range
Set ws = ActiveWorkbook.Sheets("Use
For Each wscell In ws.Range("B5..B10")
If wscell.Value = "" Then
'its empty so empty cells to right
wscell.Offset(0, 1).Resize(1, 3).ClearContents
End If
Next wscell
End Sub
Sub sumup()
Const Input_Range = 5
'3
Const Output_Range = 800
'17
Const Output_CS = 7
'18
Const Output_CE = 1
Const Input_CM = 5
Dim O_R As Long
'Output Row
Dim I_R As Long
'Input Row
Dim Total As Double
'Total
'**** Clear the previous output ****
With Worksheets("Validation_DB"
.Range("G800:G812") = ""
End With
O_R = Cells(Rows.Count, Output_CS).End(xlUp).Row
If O_R >= Output_Range Then
'Column and Row Output
Range(Cells(Output_Range, Output_CS), Cells(O_R, Output_CE)).ClearContents
End If
'Column Explanation
O_R = Output_Range
'Where the checkmark is
I_R = Cells(Rows.Count, Input_CM).End(xlUp).Row
If I_R >= Input_Range Then
'Input row
For I = Input_Range To I_R
With Cells(I, Input_CM)
If .Value = "P" Then
'Output results here
Sheets("Validation_DB").Ce
Sheets("Validation_DB").Ce
Sheets("Validation_DB").Ce
"_(""$""* #,##0_);_(""$""* (#,##0);_(""$""* ""-""??_);_(@_)"
If Not IsError(.Offset(0, 5).Value) Then Total = Total + .Offset(0, 5).Value
O_R = O_R + 1
End If
End With
Next
Cells(Output_Range - 1, Output_CS).Value = "Total"
With Cells(Output_Range - 1, Output_CE)
.FormulaR1C1 = "=SUM(R[1]C:R[" & O_R & "]C)"
.NumberFormatLocal = _
"_(""$""* #,##0_);_(""$""* (#,##0);_(""$""* ""-""??_);_(@_)"
End With
End If
'now fix the Use_Priority sheet
Dim ws As Worksheet, wscell As Range
Set ws = ActiveWorkbook.Sheets("Use
For Each wscell In ws.Range("B5..B10")
If wscell.Value = "" Then
'its empty so empty cells to right
wscell.Offset(0, 1).Resize(1, 3).ClearContents
End If
Next wscell
End Sub
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents. The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210 (2 * 3 * 5 * 7) or 2310 (2 * 3 * 5 * 7 * 11).
The larger templa…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month6 days, 21 hours left to enroll
622 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.