CFMI
asked on
MS Excel VBA to use a form field selection as a name
Experts.
I created a drop down screen so users may select a workbook and after selection, the path and the name appear in the box named: SSReportBuilder.xls.UserFo rm1.SSFile Name
While running the code (another button on the form named UserForm1) a message appears stating “Object Required”.
The code it highlights is: FullName = SSReportBuilder.xls.UserFo rm1.SSFile Name.
Since there is a need for this form to be open, I have not been able to test it inside VBA.
Please advise as to how the test may be performed or suggest an alternate way to assign a name to the selected “Full Path Excel file”
Thanks,
I created a drop down screen so users may select a workbook and after selection, the path and the name appear in the box named: SSReportBuilder.xls.UserFo
While running the code (another button on the form named UserForm1) a message appears stating “Object Required”.
The code it highlights is: FullName = SSReportBuilder.xls.UserFo
Since there is a need for this form to be open, I have not been able to test it inside VBA.
Please advise as to how the test may be performed or suggest an alternate way to assign a name to the selected “Full Path Excel file”
Thanks,
ASKER
I added the message box but while running I receive an error message, :Object Required". below displays the two statements and I attached the spreadsheet form.
FullName = SSReportBuilder.xls.UserFo rm1.SSFile Name
MsgBox FullName
SpreadsheetForm.jpg
FullName = SSReportBuilder.xls.UserFo
MsgBox FullName
SpreadsheetForm.jpg
Can you attach your workbook?
ASKER
Please see attached workbook.
SSReportBuilder.xls
SSReportBuilder.xls
Change
FullName = SSReportBuilder.xls.UserFo rm1.SSFile Name
to
FullName = UserForm1.SSFileName
What was confusing us was that FullName is an Application property (Application.FullName). You should change your variable's name to avoid confusion.
FullName = SSReportBuilder.xls.UserFo
to
FullName = UserForm1.SSFileName
What was confusing us was that FullName is an Application property (Application.FullName). You should change your variable's name to avoid confusion.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent, the VBA code worked well.
You're welcome and I'm glad I was able to help.
Marty - MVP 2009 to 2013
Marty - MVP 2009 to 2013
d:\SomeFolder\Somefile.xls
(The full drive, path, filename, and extension of the file)
I am not sure how : SSReportBuilder.xls.UserFo
...can translate into a full file name...
ad a message box after the line:
FullName = SSReportBuilder.xls.UserFo
msgbox FullName
...and see what is returned
JeffCoachman