CODEWITHSUNDEEP
excelvba
user3733731
user3733731

Reputation: 11

Calling Value From another macro

I have a userform which opens one of a list of worksheets. I want to create a macro that recognises the last sheet opened by the userform and then runs data validation based on data types in the sheet.

How can I reference the sheet opened so that it can be called later my the validation macro?

All help gratefully accepted!

Upvotes: 0

Views: 403

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

You need to "remember" it in a Public variable.

In a Standard Module, near the top of that module, include:

Public LastSheet As Worksheet

and in the UserForm, code like:

Sub WithinUserForm()
    Dim x As String
    x = Application.InputBox(Prompt:="pick a worksheet", Type:=2)
    Sheets(x).Select
    Set LastSheet = ActiveSheet
End Sub

Finally within the DV macro:

Sub MacroForDV()
    LastSheet.Select
End Sub

Upvotes: 1

Related Questions