Trouble with UserForms and Variables

[DavidSystems]

Hi all,

I new to the site and still a bit new to VBA. What I am trying to do is open a user form and allow the user to pick a file (perhaps a few files in the future) open it and display the file name with one button click. Then use another control bottun to do the work of getting the needed info and update other workbooks. To do this I am trying to make a Public Variables so I can keep referencing the file when swithcing back and forth. For some reason it crashes on the public varb.

code from the design mode

Private Sub CommandButton2_Click()
Public FileName As String
OpenFile.Show
End Sub


i tried the same process without the userform and crashed at the same place. Any suggestions on what I am doing wrong. I have look at the sysntax of a public variables and it seems to be correct.

 

[Andrew Poulsom]

Welcome to MrExcel.

The declaration of FileName needs to be at the top of a General module like Module1, before any procedures. Variables declared within a procedure are visible only to that procedure.

 

[DavidSystems]

Thank You Andrew, Wasn't expecting such a fast reply. Right now the user form is the only code i have. There are no other modules to put it in. Do you have to make at least one macro for the userform to work properly?

 

[Andrew Poulsom]

Yes, you have to insert a module. The module doesn't need to have any procedures, just the variable declaration.

 

[DavidSystems]

That did the trick to get the program to start however that particular variable is not holding its value throughout the code. I'm not sure what I’m doing wrong but here is the 2 places the FileName is being used. The error is in the 2nd sub and is highlighted. During debug that variable is empty. If I declared it public in module should it holds its value?</SPAN>

Module one
---------------
Sub decvarb()
Public FileName As String</SPAN>
End Sub</SPAN>


Private Sub OpenFile_Click()
Dim filt As Variant
Dim FilterIndex As Integer
Dim Title As String
Dim FileToOpen As Variant</SPAN>

filt = "Excel Files (*.xlsx), *.xlsx," & _
"Excel Macro (*.xlsm), *xlsm," & _
"All Files (*.*), *.*"
' filt varb set the types of files that can be opened

FilterIndex = 3 ' fliter inder on how many types of files we allow the user to search

Title = "Please choose a file to import" ' message varb to tell user to pick a file</SPAN>

FileToOpen = Application.GetOpenFilename _
(filefilter:=filt, _
FilterIndex:=FilterIndex, _
Title:=Title)
'FileToOpen use the application.getopenfilename command that allows users to open a file</SPAN>
If FileToOpen = False Then
MsgBox "No file specified.", vbExclamation
Exit Sub</SPAN>
Else
ActiveSheet.Range("F16") = FileToOpen ' disply path name for user
FileName = Dir(FileToOpen) 'gets the file name
Workbooks.Open FileName:=FileToOpen 'opens the file
</SPAN>
End If
End Sub</SPAN>
-------------------------------------------------------


Private Sub Import_Click()</SPAN>
ThisWorkbook.Activate
Sheets("YYY").Select
Range("A3:BC3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

</SPAN>Windows(FileName).Activate</SPAN>
Range("D9").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

ThisWorkbook.Activate
Sheets("MerchRev").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False</SPAN>

End Sub

Thanks again</SPAN>

 

[Andrew Poulsom]

Remove the red statements:

Sub decvarb()
Public FileName As String</SPAN>
End Sub</SPAN>

 

[DavidSystems]

Once again Thank You Andrew your a great help. It worked although I am not really sure as to why. If this helps anyone to see this I will ask the question. I thought all modules had to start and end with something. sub xxx () and end sub. Perhaps it is different when using a user form. If this was a not a user form and just a collection of modules that I were accessing in the code would it be correct to go into module 1 and just declare the Public variable there? Or can you create a new macro drag it to the top and it only contain the declared variables and no code. I hope these questions help other users that had the same issue as mine.</SPAN>

 

[Andrew Poulsom]

As I said before a module doesn't need to have any procedures. It can contain only variable declarations. It's a procedure (not a module) that starts with Sub and ends with End Sub.