Replace Table's Empty Cell with data?

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15669
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Replace Table's Empty Cell with data?

Post by ChrisGreaves »

I can do this in VBA, but not as a regular user of MSWord(2003)
Untitled.png
There is a table of tasks ("ToDo") and the rightmost column serves as a checklist. When a task is done I mark it with a "Y". But while a task is not done the table cell remains in its empty state (circled yellow in my image).

As far as I can see a User presented with this problem is stymied; nothing in "Special" suggests a non-existent character.

The Super-User of course is not thwarted:-
(1) Convert the table to text, replace paragraph marks with N and a paragraph mark, convert back and then replace YN with N (or convert tab+paragraph with tab+N+paragraph before converting back)
(2) Similar approaches such as Insert a column to the right, fill it with N (how?) and then merge cells followed by the traditional replace YN with N

What solutions can our super-users come up with? The solution will be for those many poor souls who use MSWord but are NOT allowed to record macros, let alone write VBA.

This problem came up this morning, but of course it triggered thoughts of Tom Scott's video with line-breaks and space in the Ogham script.

Cheers, Chris
P.S. Assume a huge table; this is my document Rebuild077, an accumulation of tips and tricks over the past 76 rebuilds. C
P.P.S. I made myself a note in the document years ago:-
Do NOT rebuild this table; to do so is to lose all the hyperlinks! As will re-saving it as DOC from RTF.
So strictly speaking, options (1) and (2) are not workable in this tab;e's case - there are many hyperlinks. C
You do not have the required permissions to view the files attached to this post.
He who plants a seed, plants life.

User avatar
StuartR
Administrator
Posts: 12635
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Replace Table's Empty Cell with data?

Post by StuartR »

When you use Wildcards you can specify NOT, but this doesn't help as [!Y] matches any character that isn't Y, but these cells don't have any character in them!
StuartR


User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15669
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Replace Table's Empty Cell with data?

Post by ChrisGreaves »

StuartR wrote:
16 Mar 2023, 15:11
When you use Wildcards you can specify NOT, but this doesn't help as [!Y] matches any character that isn't Y, but these cells don't have any character in them!
Thanks for this confirmation, Stuart.
Now we all know how they felt hundreds of years ago before "zero" was invented.

I mainly surprised that I haven't felt the next for "replacing empty cells" until now. tables are a big part of this user's life.
Cheers, Chris
He who plants a seed, plants life.

User avatar
Charles Kenyon
5StarLounger
Posts: 636
Joined: 10 Jan 2016, 15:56
Location: Madison, Wisconsin

Re: Replace Table's Empty Cell with data?

Post by Charles Kenyon »

You could put a MacroButton field in that pops up a question. (requires macros)

You could put a formfield dropdown with Yes and No as choices, perhaps Yes/No as the first of three choices. (requires locked form)

You could use a checkbox as is used in the Fax template that is really a macrobutton field with AutoText. (requires macros)
[url]https-://www.addbalance.com/word/download.htm#CheckboxAddIn[/url]
http://www.addbalance.com/word/checkbox.htm

But, you can't simply use Replace. Even if you could, it would not be obvious.

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15669
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Replace Table's Empty Cell with data?

Post by ChrisGreaves »

Charles Kenyon wrote:
18 Mar 2023, 15:35
You could put a MacroButton field in that pops up a question. (requires macros)
You could put a formfield dropdown with Yes and No as choices, perhaps Yes/No as the first of three choices. (requires locked form)
You could use a checkbox as is used in the Fax template that is really a macrobutton field with AutoText. (requires macros)
[url]https-://www.addbalance.com/word/download.htm#CheckboxAddIn[/url]
http://www.addbalance.com/word/checkbox.htm
But, you can't simply use Replace. Even if you could, it would not be obvious.
Thanks Charles. If I have understood this, these solutions all require some degree of VBA. I could simply record a macro to insert "N" in each cell of the column, and then use edit-replace to /YN/Y/.
I was thinking here of how someone with knowledge only of Word (as an end-user) and who is forbidden to write record/macros might be able to accomplish the task.
Such an inexperienced user (e.g. me!) might have set up the table, populated some cells with "Y" ("Yes, task completed"), and then start thinking, "Out of these 200 cells in the column, how do I locate the cells in the rightmost column that are NOT completed?".

I could sort the table on that column, grouping all the empty cells together. That would make it easy to manually type "N, <down-arrow>; but in my case the right hand column is a {SEQ}, so I can't very well sort the table back to its original sequence.
Yes I could. I could save the original copy with its {seq}, then in a second copy fix the {SEQ}, sort on the "Yes" column, manually populate with "N" values, sort back in the original sequence using the fixed {SEQ} values, and then paste the new "Yes" column to the right of the original table and delete the now-redundant column.
Phew!
Untitled.jpg
Cheers, Chris
You do not have the required permissions to view the files attached to this post.
He who plants a seed, plants life.

User avatar
Charles Kenyon
5StarLounger
Posts: 636
Joined: 10 Jan 2016, 15:56
Location: Madison, Wisconsin

Re: Replace Table's Empty Cell with data?

Post by Charles Kenyon »

Chris, you are far from a macro neophyte!

Download and take a look at my Add-In. It is a .dot file.
When installed, the user need not know anything about macros.
When the macros are used in a document template, the user of a document attached to that template need know nothing of macros or AutoText.

Take a look also at Greg Maxey's tool: https://gregmaxey.com/word_tip_pages/find_it.html. It does not answer this question, but you will find it useful.

Here is another that you can use: https://gregmaxey.com/word_tip_pages/se ... ering.html

Both of those are for Word 2003.

User avatar
Charles Kenyon
5StarLounger
Posts: 636
Joined: 10 Jan 2016, 15:56
Location: Madison, Wisconsin

Re: Replace Table's Empty Cell with data?

Post by Charles Kenyon »

ChrisGreaves wrote:
19 Mar 2023, 14:06
Charles Kenyon wrote:
18 Mar 2023, 15:35
You could put a MacroButton field in that pops up a question. (requires macros)
You could put a formfield dropdown with Yes and No as choices, perhaps Yes/No as the first of three choices. (requires locked form)
You could use a checkbox as is used in the Fax template that is really a macrobutton field with AutoText. (requires macros)
[url]https-://www.addbalance.com/word/download.htm#CheckboxAddIn[/url]
http://www.addbalance.com/word/checkbox.htm
But, you can't simply use Replace. Even if you could, it would not be obvious.
Thanks Charles. If I have understood this, these solutions all require some degree of VBA. I could simply record a macro to insert "N" in each cell of the column, and then use edit-replace to /YN/Y/.
I was thinking here of how someone with knowledge only of Word (as an end-user) and who is forbidden to write record/macros might be able to accomplish the task.
Such an inexperienced user (e.g. me!) might have set up the table, populated some cells with "Y" ("Yes, task completed"), and then start thinking, "Out of these 200 cells in the column, how do I locate the cells in the rightmost column that are NOT completed?".

I could sort the table on that column, grouping all the empty cells together. That would make it easy to manually type "N, <down-arrow>; but in my case the right hand column is a {SEQ}, so I can't very well sort the table back to its original sequence.
Yes I could. I could save the original copy with its {seq}, then in a second copy fix the {SEQ}, sort on the "Yes" column, manually populate with "N" values, sort back in the original sequence using the fixed {SEQ} values, and then paste the new "Yes" column to the right of the original table and delete the now-redundant column.
Phew!
Untitled.jpg
Cheers, Chris
Again, you are far from a Macro neophyte.

You need MacroButton fields that call on macros.
You save the MacroButton fields as AutoText.

These fields have display text that can be a checkbox. It can be a Y or N, or anything else you want. When you click on the field, it runs a macro.
The working of MacroButton fields is that when you click on them, they select the entire field.
The macro replaces one MacroButton field with another that has the toggled display. I.e., if it say "N" or "No" when you click on it, it runs a macro that replaces that field with one that says "Y" or "Yes." That field will run the other macro if clicked on.
You use AutoText so that your macro does not need to recreate the field with vba, which is a pain, especially if your display is not text but an image of some sort.

This technology dates back to some of the templates distributed with Word 97. Again, it is invisible to the end user.
As for identifying things that are not completed, you could have completed/uncompleted character styles. Any time you wanted to see one or the other, change the style to have a different color, a different font size, a different font, even! You could have a macro that does this, as well.

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15669
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Replace Table's Empty Cell with data?

Post by ChrisGreaves »

Charles Kenyon wrote:
19 Mar 2023, 18:12
Chris, you are far from a macro neophyte!
Thank you Charles (but I already knew that! :laugh: :laugh: :laugh: )

I was thinking of the beginner, or the employee of a frightened corporation.
Untitled.jpg
The beginner is starting to explore MSWord's menu system, and is overwhelmed by the features available. Just in Word2003's Edit, Find/Replace once they choose "Special" the options can be overwhelming. And the Special menu changes between Find and Replace! The beginner will, if under my care, soon get to recorded macros and VBA, but obviously they are still at the stage of mastering tables and designing data items. Now they have just learned that they should have populated the column with "N" or "<space>" rather than leaving it empty. Also that they should have run through all their setup before adding in 200 rows of data. Life is tough for a beginner. I should know. I just moved to Win11.

Under contract to large corporations in Toronto's GTA I all too often sat in a cubicle with a clapped-out desktop computer and an absolute ban on macros. Upper management was middle management before 1997 and remembered [sound effects on]The Melissa Virus[sound effects off], and maintained an irrational fear of macros and VBA. In two cases, once for a pharmaceutical in Mississauga, then for one of Canada's largest banks in Toronto, I was hired for my deep knowledge of VBA (as they saw it), and once I had started work was told that management had banned VBA. Go Figure!

No question in my mind that VBA cures many MSWord problems. Woody Leonhard in "The Underground Guide to Word for Windows" made that point in the first Chapter. Even with an appeal to management ("Let's make this book pay for itself").

So, to me it is an MSWord problem to be solved by an on-the-fly macro: Tools, Macro, Record, Keyboard, Shift+Ctrl+K, <Enter> and away we go. And how I enjoy teaching that.
But I always start by seeing if the task can be done in raw MSWord.

Truth is I half-expected someone to reply "Grow up, Chris! ***I*** just did it in Word 2023. Get a copy!", so I am half-surprised that (it appears) it still can't be done in the latest all-singing all-dancing versions of MSWord

Cheers, Chris
You do not have the required permissions to view the files attached to this post.
He who plants a seed, plants life.

User avatar
Charles Kenyon
5StarLounger
Posts: 636
Joined: 10 Jan 2016, 15:56
Location: Madison, Wisconsin

Re: Replace Table's Empty Cell with data?

Post by Charles Kenyon »

AFAIK, find and Replace today is much the same as in Word 97.
Wildcards remain a challenge for me.

OTOH, are there corporate entities out there not using the Ribbon versions of Word?
To me, Content Controls are worth all the headaches of using the Ribbon versions. You can do forms without the horrendous lock-down of other features.