VBA: Activating Certain Cells only When Sheet Added and Data

    ok, I think this will be a good question for some guru out there! I have a sheet that is created with VBA code only as needed in the workbook. Some of the cells on this newly created sheet are controlled by Data Validation. After the sheet has been created, the user selects an item from a list.


    After that selection has been made, the spreadsheet needs to record that selection on another (always existing) sheet from which other calculations are then done.


    Of course, when I try to use a formula on the aforementioned sheet, I get a #N/A since the control sheet has not been created yet. If I try to use a user function, SheetExists, I seem to create links to other worksheets. This is not acceptable. Then when I break the links, the formula is eliminated and I am back to square one.


    I hope I have been clear and also hope someone has a good answer.


    It is hard to post this since it is quite a large template and because the contents are very sensitive to our industry and I am not allowed to share a lot of the formulas contained in it. The workbook template is engineering oriented if that means anything to anyone...


    Thanks very much!

    Thanks,


    Blue

    Hi Blue


    The function method should work. Try nesting this one into your formulae



    Code
    Function SheetExists(Sheet_name As String) As Boolean
Dim wSheet As Worksheet
Application.Volatile
On Error Resume Next
    Set wSheet = Worksheets(Sheet_name)
    SheetExists = Not wSheet Is Nothing
End Function

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login