[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 439
  • Last Modified:

Flag it Correctly - VBA

1.      User will enter a passcode in cell B9 of sheet [front] to be able to see specific enabled toggle buttons on that worksheet
2.      After this happens, some VBA will kick in the background and update the data in the ConsolidatedTable Sheet (This is where I need help)

3.      Passcode (in BackEndControl sheet) can be assigned specific enabled toggle buttons
4.      While assigning passcodes - Each passcode can have an attribute called “SpecialPass”
a.      SpecialPass values can be a valid string like t1,t2,t3 or it could simply be “”







So the VBA needed for the purpose is described below...
In the worksheet “ConsolidatedTable - ”Go through column F (Flag This) and put “Yes” in the cell ONLY if its satisfies this condition set:
1.      If for the Same Row - column C has a name that matches with enabled toggle button on worksheet [front]
a.       if for the same row – if you see a a valid specialPass value (like t1,t2,t3) then only flag that row else NOT
b.      If for the same row, you see a “”, then ok
c.      If for the same row, you see specialPass = a valid value that is not assigned to the passcode, DON’T flag the row

For example:
1)       I created a passcode “6789” in sheet “BackEndControl” and assigned it two toggle buttons (B1,B2). Further, I assigned it a specialPass value of t3.
2)      Then I added 6789 in cell B9 of sheet “front” to see the enabled buttons for that passcode
3)      Now what I need >> a vba code that goes through the column F of “Consolidated Table”, then it puts “YES” in the row that qualifies to be flagged  under the above  condition
a.      By default, if button name on column C matches with a button that’s enabled and specialPass is “”, then put “Yes” column F
b.      If button name on column C matches with a button that’s enabled and special pass column in consolidated table has  [t3]  - same as the specialPass value of that password (cell B9 of BackEndControl sheet) – FLAG it else NOT
c.      SpecialPass is special code that must be looked at
d.      If say the passcode has t2 as specialPass in sheet [BackEndControl], then only, the rows of consolidated table having that specialPass = t2 are flagged
e.      If say the passcode has t3 as specialPass in sheet [BackEndControl], then only, the rows of consolidated table having that specialPass = t3 are flagged
f.      If say the passcode has “” as specialPass in sheet [BackEndControl], then, the rows of consolidated table having that specialPass = “” are flaggedCondition






Logic
even through B1 is enabled, but specialPass is Not t3, so don’t flag it
B1 enabled and specialPass = "", so flag it
B1 enabled and specialPass = "", so flag it
B3 is Not enabled and specialPass  = "", so don’t flag it
B3 is Not enabled and specialPass  = "", so don’t flag it
B3 is Not enabled and specialPass  = "t2", so don’t flag it
B2 is enabled but specialPass is NOT t3, so Don’t Flag it
B2 is enabled and specialPass is "t3", so Flag it
B2 is enabled and specialPass is "", so Flag it
B6 is not enabled, so don’t flag it
B7 is not enabled, so don’t flag it


Thank you
flaggingIssue.xlsm
0
Rayne
Asked:
Rayne
  • 6
  • 4
1 Solution
 
RayneAuthor Commented:
It’s a small vba code that I need assistance in  but sorry for a  lot of lines and explanation  but I wanted to make it very  clear in exaplantion. …Let me know if you have further questions,
Thank you
0
 
RayneAuthor Commented:
I can make this into a several separate questions if that help. Let me know …No worries for the point – I am very generous :)
0
 
Ken ButtersCommented:
Attached is spreadsheet with requested vba code.
flaggingIssue.xlsm
0
Industry Leaders: 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!

 
RayneAuthor Commented:
Hello buttersk,

thank you for the reply so far. I see two issues:

I saw that you hard-coded the button name into the vba  - something is is not recommended as the buttons will be growing and I don't want the users to touch the vba code as well when they are adding new buttons.

secondly, this is a speed question - if I am dealing with 30000 rows - would this be the fastest approach ?

Thanks
0
 
Ken ButtersCommented:
Issue 1:
I saw that you hard-coded the button name into the vba  - something is is not recommended as the buttons will be growing and I don't want the users to touch the vba code as well when they are adding new buttons.

I needed set up a relationship between the buttons on the sheet "Front" and the table columns on sheet "BackendControl".

your button names were:
TB_B1
TB_B2
TB_B3
TB_B4
TB_B5
TB_B6

Your table column name was
B1
B2
B3
B4
B5
B6

can you confirm that the button naming convention will always be prefixed with "TB_" and match the column name of the table on sheet "BackEndControl" ?

so button number 75 for example... would be called TB_75?

As far as speed... what is gonig to have 30,000 rows?   if you are talking about 30,000 rows in consolidated tabe... then this code will not be impacted by that size.

If you are talking about 30,000 rows in backendControl table... then yes... that will impact performance because I am doing a vlookup on that table.  

If that table becomes large and performance an issue... then might need to change the lookup method to store that data in a collection or in a dictionary.
0
 
RayneAuthor Commented:
Hello buttersk
Thank you for following up.
can you confirm that the button naming convention will always be prefixed with "TB_" and match the column name of the table on sheet "BackEndControl" ?
YES

so button number 75 for example... would be called TB_75?
YES

As far as speed... what is going to have 30,000 rows?   if you are talking about 30,000 rows in consolidated tabe... then this code will not be impacted by that size.
AWESOME. Perfect



If that table becomes large and performance an issue... then might need to change the lookup method to store that data in a collection or in a dictionary.
 - This will be a separate question for more points as I want to reward you for the current work you are doing
0
 
Ken ButtersCommented:
Attached code updated to remove hard coding for buttons.

Code assumes that buttons will be named

TB_B1
TB_B2
TB_B3 etc...

also assume that button names will start with TB_B1 and will increment sequentially... (won't skip numbers).

Also assumes that consolidatedTable in Column "C" will name corresponding buttons as
B1
B2
B3.... etc...
0
 
RayneAuthor Commented:
Hello buttersk

I dont see the file
0
 
Ken ButtersCommented:
sorry... here it is.
flaggingIssue.xlsm
0
 
RayneAuthor Commented:
Perfect :)
This works, thank you
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now