Posts by jotoya

Thank you rabsofty and Infomage for your feedback I do appreciate it.

I'll have to apologize because I do not believe I am communicating what I am looking for well enough. Let's see if I can do better: So rabsofty, you are in the right path, the ActiveWindow.DisplayWorkbookTabs is the command I am utilizing which of course is the VBA equivalent of the pic I posted regarding the route of File | Options...and so forth. I am aware that the latter (VBA or otherwise) does not remove the tabs but merely removes them from the user's sight. Infomage, I am actually utilizing the very hidden method and it is not what I am desiring to do.

So back to the ActiveWindow.DisplayWorkbookTabs command - I am looking to use this very command BUT, I need to execute (via VBA) **IF** the user uses the File | Options | Advanced | Display options for this workbook to manually attempt to display the tabs again. In other words, let's say I'm the user and I want to "peek" into the tabs of the workbook and I have enough knowledge to make the sheet tabs appear/show via the File | Options | Advanced | Display options for this workbook check box <-- **THIS** is what I am attempting to prevent. Once the user checks the latter box and clicks OK, I need for the workbook to "look and feel" that the tabs are now displaying, provide a "naughty user" message box, and, re-hide them again.

I've attempted to use the following workbook event but, unfortunately, the code will only fire once the sheet becomes active - which I suppose will work, but, I'd rather have the code fire BEFORE than after. I hope I've explained it better and it makes better sense:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)


   If ActiveWindow.DisplayWorkbookTabs = True Then
     ActiveWindow.DisplayWorkbookTabs = False
   End If


End Sub

Hi Robert and welcome to OzGrid,

Your post is a bit confusing. So the macro looks for a value in Column A in a sheet with the name "A" AND in column A in sheet "B" and if the value is found it replaces it in both sheets??

Thank you rabsofty. I was actually fearing that my post would be misunderstood.

My workbook is already protected at the sheet and workbook level. I am aware that when a workbook is protected, the ability to hide or unhide sheet tabs is removed. I should have used Display/Not Display sheet tabs as my post title.

So, if a user goes to File | Options | Advanced | Display options for this workbook, he/she can make all of the sheets show or not show regardless whether workbook protection is active or not, hence, removing from the user the ability to navigate through the workbook by clicking on whatever tabs are visible. I hope this makes sense.

My question was utilizing the angle that if a user uses the File | Options | Advanced | Display options for this workbook option to check the "Show sheet tabs" option and then clicks OK, if there is a way for the workbook to recognize that the activesheet is being asked to show/display the tabs and if so, through VBA to prevent it.

[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"DisplaySheetTabs.jpg","data-attachmentid":1197916}[/ATTACH]

Hi -

I currently have my sheet tabs hidden. What I'd like to do is: If a user attempts to unhide the tabs via the File | Options menu in Excel, for a macro to fire up and rehide the sheet tabs immediately. Nothing has to change on the active sheet, the only trigger being if the user simply attempts to unhide the sheet tabs. Any help/guidance is greatly appreciated. Thank you!

Jose

Re: Count actual word in cell

I will for sure test it KjBox! THANK YOU!! I didn't realize it was that late for you PLEASE get some rest. I'll for sure post back!

Re: Count actual word in cell

Quote from KjBox;799154

I was busy writing a new UDF for you when I had a brainwave!

Because you need to find instances where the value entered in K2 is a whole word only, rather than a whole word or part of a whole word, then if you do the following your array formula will work.

Instead of entering "Sin" in K2 enter " Sin "

A leading and trailing space means that only whole words will be counted.

Display More

You are right KjBox! Not to be a kill joy in the least but the only issue would be if there is a "period" or say, "a question mark/exclamation mark" following whatever word is entered in K2. But, this is the absolute closest the formula has gotten - thanks to your suggestion! Continuing with the use of the word "Sin", it appears a total of 7 times so 4 is super close. The 3 instances that the "space fix" does not catch are periods and question marks.

Skywriter noted before he vowed out that his UDF was not the issue. I took this to mean that since his code was not the problem that there is still a way to incorporated into an array formula.

Any chance I can still get the UDF you were working on? Again, super grateful for taking the time to work with me on this. I REALLY appreciate it as this workbook is a valuable study tool for me. Thank you!!

Re: Count actual word in cell

Quote from KjBox;799102

Try putting it in Dropbox.com and post the link to it here.

Hope this works. Thanks KjBox: https://www.dropbox.com/s/h92z…kv7/Bible%20KJV.xlsm?dl=0

Re: Count actual word in cell

Hi KjBox -

I have tried IE, FF and Chrome to no avail in my attempts to upload the file. I have followed the directions you provided to the T. I have tried "Drag-n-Drop" only to have the file open in my "Downloads" folder. I can only get as far as browsing for the file. But, when I click "Upload" I get a dialog that says: Upload of file failed.

At first I thought it was just my job's network but apparently it is not. I have been trying for the last 20 minutes to upload this from home. Any other way I can send it?

Re: Count actual word in cell

I apologize KjBox, I just saw your reply. Thank you much for offering further assistance. I have tried multiple iterations to no avail - yet.

The array formula resides in the "Stats" sheet under the "Occurrences of the word" table column with the lookup word located in cell K2. I have already added skywriter's UDF to the WB.

I am having problems uploading to Ozgrid at the moment will try in a bit.

Thanks again KjBox!

Re: Count actual word in cell

Sounds good. Thank you.

Re: Count actual word in cell

Hi skywriter -

Again, thank you for the UDF! Unfortunately, I am not able to work it into an array formula. I've tried several combinations to no avail. The formula I shared in the original post looks across all sheets and returns an answer albeit not the wanted one. Perhaps I am missing something very obvious. Any thoughts?

Re: Count actual word in cell

Thank you skywriter! That is very generous of you to include a sample workbook. I will play around with the UDF and the array formula and post back an update.

Hello all!

I am using an array formula to seek, within cells of each sheet, whatever word I type in to cell K2 in my workbook. Here's the formula:

{=SUM(LEN(UPPER(INDIRECT("'"&H8&"'!B1:B"&I8)))-LEN(SUBSTITUTE(UPPER(INDIRECT("'"&H8&"'!B1:B"&I8)),UPPER($K$2),"")))/LEN($K$2)}

Let's say I am want to look up the word "Pick". The above formula works great BUT, I am ONLY interested in finding the word "Pick", not "PickING" or "PickED" or "PickLED" which the above will find and include. I have been fiddling with different portions of the formula so it only selects the word "Pick" (in my example) and disregard all others; adding parameters to the formula to "look" for spaces pre/post the search word?

Thank you all so much!

Re: MsgBox if Slicer item not selected

Yes. But YOU did 99% of the lifting! Thank you!!

Re: MsgBox if Slicer item not selected

Hi dotchiejack,

I apologize for not telling you thank you for your suggestion. I appreciate you taking the time to reply.

Re: MsgBox if Slicer item not selected

Quote from KjBox;796149

Maybe

Code

Sub NextBtn()
    Dim sItem As SlicerItem, DtCache As SlicerCache, bItemSelected As Boolean
     
    Set DtCache = ActiveWorkbook.SlicerCaches("Slicer_DATE")
    bItemSelected = 0
    
    For Each sItem In DtCache.SlicerItems
        If [COLOR=#ff0000][B]Not [/B][/COLOR]sItem.Selected = True Then
            Range("RegisterDate").Value = sItem.Name
            bItemSelected = 1
            Exit For
        End If
    Next sItem


    If bItemSelected Then
        DtCache.ClearManualFilter
        Worksheets(3).Activate
        Range("RegisterLoc").Select
    Else
        MsgBox "No Slicer Item Selected", , "Slicer Selection"
    End If


    Set DtCache = Nothing
    
End Sub

Display More

I got it! I got it! All your code needed was a "Not" in the If statement!! I REALLY hope someone else can benefit from this. Thanks again KjBox

Re: MsgBox if Slicer item not selected

Hello KjBox!

First things first: I apologize for not responding promptly to your suggestion. Turns out that my workbook got corrupted and I had to start from scratch again. At any rate, I got to the point of using the slicer code you graciously suggested and unfortunately it did not work.

It's interesting to, because an error is never fired and the code executes as though there was not an "Else - No Slicer item selected" in the code though it is. I am still at a loss. It seems that a "False" value is completely overlooked by the code. Again, I sure do appreciate your assistance.

If there is anyone else that may have encountered this "error" and has a solution please let me know. I would greatly appreciate it. I'll check out other forums to see what I can find.

Good evening OzGrid community,

I am new to this community and would like to thank you in advance for any assistance.

Before I post my question, and, as a courtesy to the Mr.Excel.com community, I did post a similar post in their forum but unfortunately it went unanswered. The question I am posing here is a bit similar but it addresses another issue.

So, here it goes - I have a slicer in which I have the following code:

Sub NextBtn()
Dim sItem As SlicerItem
Dim DtCache As SlicerCache




Set DtCache = ActiveWorkbook.SlicerCaches("Slicer_DATE")




    For Each sItem In DtCache.SlicerItems
        If sItem.Selected = True Then
            Range("RegisterDate").Value = sItem.Name
            Exit For
        End If
    Next sItem




DtCache.ClearManualFilter




Set DtCache = Nothing




Worksheets(3).Activate
Range("RegisterLoc").Select
End Sub

Essentially, the code loops through the "Slicer_DATE" slicer items, grabs the selected item, and, inserts the slicer item information into the "RegisterDate" named range cell. Now, the code, as is works great. However, what I would like the code to do (already tried to no avail) is to return a message box alerting the user if they have not selected an item and exit the subroutine.

If I used the line:

If sItem.Selected = False Then Msgbox MyMsg

Though I do not receive an error message, the code will fire the message box but, it will still insert the first value of the slicer into the named range.

Please let me know if further clarification is needed. I am super, super grateful for any assistance.

Thanks again.