how to tell if a table exists in a worksheet

**dmcgov** · 08-22-2017, 10:56 AM

how can i tell if a table (there will be only 1 per sheet) exists in a worksheet.

i have tried the following:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

how can i get this to work?

**JOHN H. DAVIS** · 08-22-2017, 11:04 AM

What do you want to do if it does?
Maybe:

Please Login or Register  to view this content.

**dmcgov** · 08-22-2017, 11:14 AM

so i used what you said and did the following:

Please Login or Register  to view this content.

but the message box never appears, the target.clear doesn't do it and finally it doesn't make the table. what am i doing wrong?

to answer your question, if there is a table, do nothing. if there is no table, then create one and give a total row.

**dmcgov** · 08-22-2017, 11:23 AM

i also tried the following:

Please Login or Register  to view this content.

**xlnitwit** · 08-22-2017, 11:26 AM

Hi,

It's

Please Login or Register  to view this content.

and not what you have which is

Please Login or Register  to view this content.

**JOHN H. DAVIS** · 08-22-2017, 11:26 AM

Where is your code? Is it Sheet Code? Where does Target.ClearContents come from? Can you post the entire code?

**dmcgov** · 08-22-2017, 11:30 AM

xlnitwit. yup i see that now. :-(

john, here is the entire sheet code:

Please Login or Register  to view this content.

the case statement "Make Table" is the code that i am looking for. does this help?

**xlnitwit** · 08-22-2017, 11:34 AM

Perhaps

Please Login or Register  to view this content.

If the MakeTableWithTotalRow routine is in the same workbook, you should call it directly instead of using Run. Also, there is no point to using Range(Target.Address) since it just returns the same range as Target.

**dmcgov** · 08-22-2017, 11:38 AM

i tried that and nothing happened. i verified that there is no table on the sheet. i would like to upload a sample workbook but have to get rid of all the data. give me a minute to do that.

**xlnitwit** · 08-22-2017, 11:41 AM

At the very least you might post the code for MakeTableWithTotalRow?

**JOHN H. DAVIS** · 08-22-2017, 11:45 AM

Maybe?

Please Login or Register  to view this content.

**dmcgov** · 08-22-2017, 11:56 AM

john, i tried that and it appears to do nothing.

xlnitwit, here is the code for maketablewithtotalrow but now i can see that it needs to be fixed. i originally created a table called prtable. this worked when it was just a macro and not fired from row k as an action. so i am sure this needs to be recoded.

Please Login or Register  to view this content.

im going to recode for just a messagebox to see if the action is working as expected.

**xlnitwit** · 08-22-2017, 12:13 PM

Perhaps this version

Please Login or Register  to view this content.

**dmcgov** · 08-22-2017, 12:53 PM

so made some progress with your help.

john, made clerical error (i so hate spaces), now routine is working as you wrote it.

xlnitwit,

thanks for that code. made a few changes and it looks like this:

Please Login or Register  to view this content.

wanted to account for dynamic data (not a fixed row) so lastrow does that. so that helps to make this action work as expected.

thanks to both of you.

**JOHN H. DAVIS** · 08-22-2017, 03:05 PM

You're welcome. Glad I could contribute, and thanks for the feedback and reps.

**dmcgov** · 08-23-2017, 07:31 AM

so im working on the update notes case. got the if statement working for either table or range. but i need help with the range part. tried to do offset, but couldn't make the syntax correct. what am i doing wrong?

Please Login or Register  to view this content.

so the target.address correctly identifies the changed cell(col K), but the field i want to update is in column G so that is why i tried the offset that i did.

any help is appreciated.

**xlnitwit** · 08-23-2017, 07:40 AM

Target is a range object so you only need

Please Login or Register  to view this content.

**dmcgov** · 08-23-2017, 07:52 AM

thanks xlnitwit, that worked.

i also found this to work as well

Please Login or Register  to view this content.

**dmcgov** · 08-23-2017, 08:25 AM

so i put the following code in my "Closed POs" sheet.

[CODE]
Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range

Dim FullExcelPath As String

FullExcelPath = ThisWorkbook.FullName
'MsgBox FullExcelPath

Application.DisplayAlerts = False

' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("d:d")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
Select Case Target.Value
Case Is < Now - 30
MsgBox "Move to Closed PO archive file"
Case Is >= Now - 30
MsgBox "Keep for now"
Case Else
MsgBox "Case Else"
End Select
End If
End Sub
/CODE]

so the stub works as expected, when i change dates it goes through the case statement properly. but what i really want is for this to be a OPEN worksheet event as well. it should check the Aging date in column D and see if its a month old or older.

i figure this should be a worksheet event but im not sure of how to transfer the code in the stub to the ThisWorkbook code sheet.

thoughts?

**JOHN H. DAVIS** · 08-23-2017, 09:23 AM

There is a Workbook Sheet Change Event which is appied to every sheet if that's what you're looking for?

Please Login or Register  to view this content.

Or if you're looking too activate the sheet on Workbooks Open Event then:

Please Login or Register  to view this content.

**dmcgov** · 08-23-2017, 09:42 AM

John,

Neither work as I expect. The first didnt work as expected, and the second error'ed out with a "run time error - 1004, Activate method of worksheet class failed" code. Let me explain what i am looking for.

i have a worksheet change event that fires on the "Closed POs" sheet. the keycell is column D. and it works as expected, when i change a date, i get a messagebox. which is what i want for this stub. So lets say that i put a date in D2 that is 7/25/17 which is one day less than the 30 (in my code) so the message box says to "Keep for Now". Then I save the file. When I return to the file the next day and open it, i want the workbook open event to check all the cells in column d to see if they need to get moved to the closed archived file or just leave it alone (and give me a messagebox).

How can that be done?

**dmcgov** · 08-23-2017, 10:53 AM

so i realize that the code will have to be completely overhauled. cant use intersect cause the date might already be old without changing the value on the sheet. the code would have to look at just the "Closed POs" sheet, examine each cell in the target.range and then do a messagebox. how can i do that?

**dmcgov** · 08-24-2017, 07:36 AM

so this last question doesn't go with the OP, so im going to close this and open up a new case.

thanks to everyone for their help.

how to tell if a table exists in a worksheet

Re: how to tell if a table exists in a worksheet

Re: how to tell if a table exists in a worksheet

Re: how to tell if a table exists in a worksheet

Re: how to tell if a table exists in a worksheet

Re: how to tell if a table exists in a worksheet

Re: how to tell if a table exists in a worksheet

Re: how to tell if a table exists in a worksheet

Re: how to tell if a table exists in a worksheet

Re: how to tell if a table exists in a worksheet

Re: how to tell if a table exists in a worksheet

Re: how to tell if a table exists in a worksheet

Re: how to tell if a table exists in a worksheet

Re: how to tell if a table exists in a worksheet

Re: how to tell if a table exists in a worksheet

Re: how to tell if a table exists in a worksheet

Re: how to tell if a table exists in a worksheet

Re: how to tell if a table exists in a worksheet

Re: how to tell if a table exists in a worksheet

Re: how to tell if a table exists in a worksheet

Re: how to tell if a table exists in a worksheet

Re: how to tell if a table exists in a worksheet

Re: how to tell if a table exists in a worksheet

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Can't name a table as the name exists, but it does not!?

[SOLVED] If Worksheet Exists

Bookmarks

Bookmarks

Posting Permissions