CODEWITHSUNDEEP
vbaexcel
dagan
dagan

Reputation: 215

Store User input and use for another subprocess

I have two sub processes that are dependant on each other .

The first one (Consolidate) gets user selected files and opens them and consolidates into the current sheet. The variable "Files" stores the path and file names selected by the user

After the user does some changes in the consolidated sheet and pressses a button on the sheet which is assinged to the "Update" macro.

The second sub (Update) uses the same input already provided by the user "Files" and finds the sheet where the updated cell is and pastes the new value .

Please suggest a way to to use the user input for "Update " sub process. 

Sub Consolidate()
Dim Files as Variant
Files = Application.GetOpenFilename("Excel Files (*.xl*), *.xl*", Title:="Select files         to Consolidate", MultiSelect:=True)
For Z = LBound(Files) To UBound(Files)
    tem = Split(Files(Z), "\")
    If (tem(UBound(tem)) <> ThisWorkbook.Name) Then

* code to consolidate* 
end sub

Sub update()

Call gotoLastModified
Application.ScreenUpdating = True
Application.DisplayAlerts = False

For Z = LBound(Files) To UBound(Files)
tem = Split(Files(Z), "\")
* code to update sheet

end sub

Upvotes: 1

Views: 637

Answers (3)

Bernard Saucier
Bernard Saucier

Reputation: 2270

You need to use global variables. To do so, declare the variable outside the routine and it will be available for all the functions to view and edit the content.

Try this to familiarize yourself: 

Dim files As Variant

Sub Consolidate()
files = 1
End Sub

Sub Update()
MsgBox files
End Sub

If you run Update() first, the MsgBox will come up empty because files is not set yet. If you run Consolidate(), then run Update() again, you will see files is updated.

Upvotes: 2

StuartLC
StuartLC

Reputation: 107247

If you convert Consolidate to a Function, and modify update to take a parameter:

Function Consolidate() as Variant
    ...
    Consolidate = Files
End Function

Sub update(Files as Variant)
    ...
End Sub

You can then pass the results of Consolidate to update

Upvotes: 1

Dane I
Dane I

Reputation: 742

I believe if you change the Dim Files as Variant line to: 

Public Files as Variant

and move it to prior to the first sub this should work. Moving it outside the sub and making it public should make it available to all subs in the macro.

Upvotes: 0

Related Questions