remove unwanted words

[GrabberHackman]

Good evening all of you my dear friends, I have a problem, which I try to explain, in a simple way,
I need to delete some words from a table, I thought I could get away with some excel formula but I couldn't, basically I have a list of words which I don't want to appear in the template below.

in the file the words I want to be deleted to better understand what I mean I will put them in red and bold.through the checkboxes that are positioned in the file you can extract the names of unwanted words and put them in a column

I hope some of you can help me

[Greg M]

Hi again Grabber!

Take a look at the attached version of your workbook and see if it does what you need.

Selecting/unselecting the "Non Preferiti" and "Intolleranze" CheckBoxes will cause the appropriate cells to be highlighted by conditional formatting or displayed as normal.

When one or more cells is highlighted a button is displayed, and this button can be used to delete all of the highlighted cells.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[GrabberHackman]

hello greg, I just tested your work and you left me speechless, the only thing I did not understand is how do I make the button for deleting words appear, also this code does not have to be implemented in the userform

[Greg M]

Hi again,

the only thing I did not understand is how do I make the button for deleting words appear

If you look at Cell BC12 (named "ptrPulsanteVisibile") you will see a formula which counts the number of cells in the range BC14:BC105 which contain the value "TRUE". If this value is greater than zero, the button is displayed, otherwise it remains hidden - the displaying/hiding is implemented via the "Worksheet_Calculate" event of the worksheet.


Glad to hear that you're pleased with the suggested approach

Regards,

Greg M

[GrabberHackman]

Wow, I didn't notice, uhmm wonder a lot if you want it to appear as a pop up

[GrabberHackman]

I am trying to insert it in mine, and in the example file the column used for the true and false was free if I want to move everything to column bh, just that I change the defined names, that you have assign you have ceclcbox, and the formula I put it in the column bh

[GrabberHackman]

update,
https://youtu.be/Ql2xAiUpMlQ
I was able to connect everything, conditional formatting is missing, because I did not understand why you indicated

Please Login or Register  to view this content.

delimiting only to this range of cells

Please Login or Register  to view this content.

then you pointed

Please Login or Register  to view this content.

delimiting to the whole field?

Please Login or Register  to view this content.

[Greg M]

Hi again,

Yes, you are right. I've found that, unless you're very careful (and obviously I wasn't), conditional formatting conditions can "grow and grow", and even though they give the correct results (appearance) the presentation (formula and range) can be difficult to understand afterwards.


You can combine the two conditional formatting conditions you indicated, as follows:

Formula:

Please Login or Register  to view this content.

Range:

Please Login or Register  to view this content.

Another point: you should amend two routines as follows:

Please Login or Register  to view this content.

This will ensure that the focus is returned to the worksheet after clicking on the Checkbox involved, otherwise the focus remains on the Checkbox, and Ribbon icons etc. will be disabled until one or more cells on the worksheet are selected.


Hope this helps - as always, please keep me informed.

Regards,

Greg M

[GrabberHackman]

Good morning greg, how are you?
So I downloaded your new file, and I saw that you have corrected the conditional format, but what is still not clear to me is the formula used, because you indicate = $ bc14 = TRUE,
my interpretation is this column bc 14 is equal to true, apply conditional formatting to the gamma, but surely this is not the case, yup? Then there is another improvement to be made, I am attaching the video

https://youtu.be/CxbP7saGXvs

[Greg M]

Hi again,

Here's a quick response to the first question:

I think the simplest way to realise how the conditional formatting formula works is to tick the "Non Preferiti" Checkbox and temporarily make the highlighted change to the existing formula:

Please Login or Register  to view this content.

You'll see that EVERY cell in each of the seven groups is now highlighted. The conditional formatting for each of these cells is now being determined by the value (TRUE) contained in Cell BC14.


When you use the original conditional formatting formula, the value which determines the highlighted/not highlighted state is taken not from the value contained in Cell BC14, but from the value contained in Column BC of the row which contains the Alimento being tested.


I'll try to take a look at your video later today.


Hope this helps.

Regards,

Greg M

[Greg M]

Hi again,

I've looked at the video, and the error message shown seems strange

The attached workbook is the same as the one I sent you previously, and I have performed the following tests without any problems:

The workbook opens with both Checkboxes unticked.

I tick the "Non Preferiti" Checkbox and press the "Cancellare" button - the highlighted values are deleted.
I untick the "Non-Preferiti" Checkbox, tick the "Intolleranze" Checkbox and press the "Cancellare" button - the highlighted values are deleted, and I close the workbook.

I open the workbook again, tick the "Intolleranze" Checkbox and press the "Cancellare button - the highlighted values are deleted.
I untick the "Intolleranze" Checkbox, tick the "Non-Preferiti" Checkbox and press the "Cancellare" button - the highlighted values are deleted, and I close the workbook.


I open the workbook again, tick the "Non-Preferiti" Checkbox, tick the "Intolleranze" Checkbox and press the "Cancellare" button - the highlighted values are deleted.



See if any error messages are generated on your computer when you perform the above tests using the attached workbook.


Regards,

Greg M

[GrabberHackman]

hello greg, I just downloaded your new file, but unfortunately it always gives me the same error I am attaching you video

https://youtu.be/LmTLw158TDw

$bc14$

I verified by blocking the cell the whole range of cells remains the conditional formatting set

[Greg M]

Hi again,

Apologies! Until I noticed the Ribbon in the video you posted, I didn't realise that your version (2007) of Excel isn't the same as mine (2013). I see now that your profile mentions Office 2007 - mi dispiace

The "DisplayFormat" property isn't available in Excel 2007, so that was the reason for the error message you received.

To overcome this problem we need a workaround - i.e., instead of checking the output (display) of the conditional formatting, we need to check the values of the cells which trigger the conditional formatting, in other words, the values of the cells in Column BC.

The following routine has been rewritten to accommodate this requirement:

Please Login or Register  to view this content.

Hope this helps - as always, please keep me informed.

Regards,

Greg M

[GrabberHackman]

Hi i just finished testing it all work and it works great, I beg your pardon, but it escaped me to specify that I had Excel 2007, thank you for your availability and for your patience.
Thank you.
I wanted to ask you for an explanation on the connection of the module "btnCancellareAlimenti", because I cannot connect it

in practice I managed to complete all the steps, except the one that allows me to operate the button to delete the foods, when I position myself on them, a new form opens for me I attach video
https://youtu.be/VK3XiEPoRvk

[Greg M]

NOTE TO ADMINISTRATORS

Just for information, when I attempted to use the word "f r o m" (without spaces) instead of "fron" (highlighted below), the firewall would not allow me to upload my post.



Hi again,

Thanks for all of your feedback.

As far as "connecting" the Cancellare button to the macro is concerned there are two options - one is to use a Forms Control CommandButton (which is what I used in the workbook I sent you), and the other is to use an ActiveX CommandButton (which, according to your video, is what you appear to have done).

With a Forms Control you simply right-click on the control, select "Assign Macro..." from the PopUp, and select the appropriate macro fron the dropdown list which appears. In general I prefer to use Forms Control CommandButtons unless I really need to use some of the extra properties provided by an ActiveX control, but that's just a personal preference.

With an ActiveX CommandButton you go to the VBA Editor, select the "Click" method of the control, and then enter the code you want the control to execute. In your case it should need only the following code:

Please Login or Register  to view this content.

and then you must change the scope of the "CancellareAlimenti" routine from Private to Public so that the above routine can find it, i.e.:

Please Login or Register  to view this content.

Your "real" workbook looks extremely interesting and I would very much like to be able to view it. If you do not wish to post it here you might be willing to email me a copy of it - if so, let me know and I'll send you my email address in a private message. I quite understand if you do not wish to share the workbook.

Hope the above helps - please continue to keep me informed.


Regards,

Greg M

[GrabberHackman]

Hi greg, again me bothering you XD, regarding "Forms Control CommandButton"
I was unable to connect it while with the actviX, everything was fine. But I am a stubborn person and I want to understand what I am wrong, it is because I cannot enter the "Forms Control CommandButton". As for the workbook I am happy that you are interested but I anticipate that it is nothing more than a simple cast sheet for macronutirinents,
write me your email in pvt, goodnight

I forgot today I'm particularly tired, if you can fix the case sensitive

[Greg M]

Hi again,

The images you posted show a UserForm CommandButton - this type of control is used only on a UserForm. Although the terminology seems misleading, Forms Controls (like ActiveX controls) are used on worksheets, but NOT on UserForms.

This is why the PopUp displayed by right-clicking on the CommandButton (on the UserForm in your image) would not have included an "Assign Macro..." option. To trigger an action from that CommandButton you have to make use of the CommandButton's "Click" event in the same way that you use the "Click" event of an ActiveX control.

Sorry about not including a case-insensitive text comparison

The highlighted change in the following routine shows how the case-insensitive comparison is implemented:

Please Login or Register  to view this content.

I'll send you my email address in a private message and will be pleased if you can send me a copy of your workbook.

Feel free to ask for more information or assistance if you need it.

Regards,

Greg M



P. S. You might find the following link useful/interesting:

https://support.microsoft.com/en-us/...0-ffb9ab94e7c2

[GrabberHackman]

Good evening Greg, I apologize for having replied to your message just now, I made your last change and what can I say, now it seems unbeatable. See the video. As for the module control button, it is still not clear to me how you set it up, I will be ***, but if I connected an "activix" button, why should I fail in this other type command?. is this once the module control button has been created that macro I will have to assign, since in your code there is no reference to the "btnCancellareAlimenti" but goes directly to the Delete foods routine? Also, as you will see from the video, I made the botton a little nicer aesthetically and I created another sheet by connecting the created date button everything seems to work I lost some time it is cursed a little on the function from Report but I succeeded in the enterprise.

[GrabberHackman]

food control installation on different sheets
https://youtu.be/uO6IK0YwT9A

case sensitive disabling
https://youtu.be/MTovRIksINM

explanation on how to connect, module control button, no activix
https://youtu.be/MTovRIksINM