Basic way to check if a table and a named range exists?

[Lil2606]

Hi all,

I'm finishing up on a project and applying "Security" I'm using Application.Undo in many shapes and forms to create a log and to not allow the users to change the layout, but allow them to edit the data..

Question 1:

I have an admin sheet (Sheet1) with something like 20 look up tables but there are 4, which are used with combo boxes in userforms, and I would like to make sure the users can't delete them, I have a

Private Sub Worksheet_Change(ByVal Target As Range)

and I would like to do an if block.. like: if "Table1" or "Table2" or "Table3" or "Table4" doesn't exist then Application.Undo
but I don't know how to do this.. possibly without using "On Error GoTo"...

Can I get some help with it please?

Question 2:

I noticed, when I copy my project over to another computer and open it up for the first time, my named range (TAX) disappears from the name manager (and my formulas give the Name error).. so I would like to add a few lines to my

Private Sub Workbook_Open()

to check if this name exists on the specific range (Sheet1.Range("C2")), and if it doesn't, it creates it. Cause the file will be located on a shared drive for the users and it would be awkward if I had to add the range 1 by 1 every time someone works with it for the first time.

Could I ask for help whith this please?

Please help!
Thanks

 

[Yongle]

In response to Question 1

Keep it simple with something like this
- ensure that the number of tables is kept up to date

Private Sub Worksheet_Change(ByVal Target As Range)
    If Me.ListObjects.Count <> [COLOR=#ff0000]20[/COLOR] Then
        With Application
            .EnableEvents = False
            .Undo
            .EnableEvents = True
        End With
    End If
End Sub

To allow you to insert and delete tables perhaps something like this ...

Private Sub Worksheet_Change(ByVal Target As Range)
[COLOR=#ff0000]    If Environ("Username") = "yongle" Then
        If MsgBox("Disable control ?", vbYesNoCancel, "") = vbYes Then Exit Sub
    End If[/COLOR]
    If Me.ListObjects.Count <> 20 Then
        With Application
            .EnableEvents = False
            .Undo
            .EnableEvents = True
        End With
    End If
End Sub

NOTE
Windows UserName: Environ("Username")
Excel UserName: Application.UserName

 

[Lil2606]

Hi Yongle, thanks the Listobjects.Count did the job perfectly I never think of that on my own!! Only to make sure I'm not hard coding it in, and to make sure more tables can be added I did it this way:

Application.Undo
        
Dim LOCP As Long 'ListObjects Count Previous

LOCP = Sheet1.ListObjects.Count

Application.Undo

Dim LOCN As Long 'ListObjects Count New
    
LOCN = Sheet1.ListObjects.Count
    
If LOCP > LOCN Then
    
Application.Undo
    
End If

I have a Security+Log sheet which logs the user name and permission type (Global Admin, Admin, User, Observer) based on what password they open the workbook with, so I have a big if block around this, starting with, if on Sheets("Sec_Log"), in the last row, C column it says "Global Admin" then exit sub. So thanks for the suggestion, I'm glad to say I did think of that already :D

 

[Yongle]

In response to Question 2
- I am puzzled that your named range vanishes
- that is not normal Excel behaviour

Here is the code you requested..
- amend NameX to the name of your named range

Place VBA below n ThisWorkbook module

Private Sub Workbook_Open()
    Call CreateNamedRange("[I][COLOR=#ff0000]NameX[/COLOR][/I]")
End Sub

Private Sub CreateNamedRange(rName As String)
    With Me
        On Error Resume Next
        .Names(rName).Delete
        .Names.Add Name:=rName, RefersTo:="=Sheet1!$C$2"
    End With
End Sub