Excel vba using table from hidden sheet in vba datavalidation

Question

How to use table from hidden sheet ?

I get error on

With Range("AI5:AI" & LastRow).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=MyTableFromHiddenSheet"
    .IgnoreBlank = True: .InCellDropdown = True: .InputTitle = vbNullString: .ErrorTitle = vbNullString: .InputMessage = vbNullString:          .ErrorMessage = vbNullString: .ShowInput = False: .ShowError = True
End With

It required to create namedrange with refernce to Table1 no direct access like in unhided sheet?

PerpetualStudent · Answer 1 · 2014-06-11T13:05:46.760

0

Are you sure you are receiving the error due to the sheet being hidden? The code appears to work properly for me even with a hidden sheet once I remove the undefined variable (in the snippet) LastRow.

To name the range (if that's the issue you're having), you can use the following (even on a hidden sheet):

Sheets("Sheet1").Range("A1:B2").Name = "NameForRange"

edited Jun 11 '14 at 13:05

answered Jun 10 '14 at 15:19

PerpetualStudent

822
9
17

if i hide a sheet, EXCEL also hide the table reference to this sheet, now i do namedrange to table – user3722021 Jun 11 '14 at 04:50
I'm sorry I don't understand. The problem you are having does not seem to be with the code you posted. The code seems to work okay. – PerpetualStudent Jun 11 '14 at 12:56

Excel vba using table from hidden sheet in vba datavalidation

1 Answers1