I have created an Excel Add-In that formerly displayed the add-in's version
and date in a message box based on values in one of the add-ins worksheets
using the following (where A1 contained the version number and A2 contained
the date last updated):

Sub DisplayVersion

Msgbox "Version "&Worksheets("Sheet1").Range("A1")& _
" updated on "&Worksheets("Sheet1").Range("A2")

End Sub

I am trying to move this function to a UserForm but can not figure out how
to get data from a worksheet into a string of text in a label within the
UserForm. Can someone point me to an example?

Thank you,

Frank Hayes

Hi
Not tested but...
If you have a Label on the form called Label1 then in the
Userform_Initialize sub (double click the form while editing and scroll
to this event) write

Label1.Caption = "Version "&
ThisWorkBook.Worksheets("Sheet1").Range("A1")& _
" updated on "&ThisWorkBook.Worksheets("Sheet1").Range("A2" )

I put in the ThisWorkBook bit as it would use the Active workbook
otherwise.

regards
Paul

Hi Frank,

Try:

Private Sub UserForm_Initialize()
With Worksheets("Sheet1")
Label1.Caption = "Version " & .Range("A1") & _
" updated on " & .Range("A2")
End With
End Sub


---
Regards,
Norman



"Frank & Pam Hayes" wrote in message
news:oE51f.13366$Vt3.12370@trnddc01...
I have created an Excel Add-In that formerly displayed the add-in's version
and date in a message box based on values in one of the add-ins worksheets
using the following (where A1 contained the version number and A2 contained
the date last updated):

Sub DisplayVersion

Msgbox "Version "&Worksheets("Sheet1").Range("A1")& _
" updated on "&Worksheets("Sheet1").Range("A2")

End Sub

I am trying to move this function to a UserForm but can not figure out
how to get data from a worksheet into a string of text in a label within
the UserForm. Can someone point me to an example?

Thank you,

Frank Hayes

Thank you both for the solutions. I really appreciate it.

Frank


"Frank & Pam Hayes" wrote in message
news:oE51f.13366$Vt3.12370@trnddc01...
I have created an Excel Add-In that formerly displayed the add-in's version
and date in a message box based on values in one of the add-ins worksheets
using the following (where A1 contained the version number and A2 contained
the date last updated):

Sub DisplayVersion

Msgbox "Version "&Worksheets("Sheet1").Range("A1")& _
" updated on "&Worksheets("Sheet1").Range("A2")

End Sub

I am trying to move this function to a UserForm but can not figure out
how to get data from a worksheet into a string of text in a label within
the UserForm. Can someone point me to an example?

Thank you,

Frank Hayes

This solution works fine in a regular workbook, but when I save it as an XLA
add-in file I get an error of the Worksheets Object. Any ideas?

Frank



"Frank & Pam Hayes" wrote in message
news:oE51f.13366$Vt3.12370@trnddc01...
I have created an Excel Add-In that formerly displayed the add-in's version
and date in a message box based on values in one of the add-ins worksheets
using the following (where A1 contained the version number and A2 contained
the date last updated):

Sub DisplayVersion

Msgbox "Version "&Worksheets("Sheet1").Range("A1")& _
" updated on "&Worksheets("Sheet1").Range("A2")

End Sub

I am trying to move this function to a UserForm but can not figure out
how to get data from a worksheet into a string of text in a label within
the UserForm. Can someone point me to an example?

Thank you,

Frank Hayes

Hi Frank,

What code are you using to display the userform and where is that code
placed?

What line of code is highlighted when your error occurs?

---
Regards,
Norman

"Frank & Pam Hayes" wrote in message
news:V7i1f.10254$Ll2.5073@trnddc04...
This solution works fine in a regular workbook, but when I save it as an
XLA add-in file I get an error of the Worksheets Object. Any ideas?

Frank



"Frank & Pam Hayes" wrote in message
news:oE51f.13366$Vt3.12370@trnddc01...
I have created an Excel Add-In that formerly displayed the add-in's
version and date in a message box based on values in one of the add-ins
worksheets using the following (where A1 contained the version number and
A2 contained the date last updated):

Sub DisplayVersion

Msgbox "Version "&Worksheets("Sheet1").Range("A1")& _
" updated on "&Worksheets("Sheet1").Range("A2")

End Sub

I am trying to move this function to a UserForm but can not figure out
how to get data from a worksheet into a string of text in a label within
the UserForm. Can someone point me to an example?

Thank you,

Frank Hayes

Norman,

My form name is AboutThis. I am calling it with the following subroutine:

Sub AboutThisForm()
AboutThis.Show
Unload About.This
End Sub

This code is placed in a module of the XLA file and is called via a custom
toolbar button that executes the AboutThisForm macro.

When the error occurs, the AboutThis.Show line is highlighted.

The exact error reads: Run Time Error '9': Subscript out of range.

Thanks,

Frank


"Norman Jones" wrote in message
...
Hi Frank,

What code are you using to display the userform and where is that code
placed?

What line of code is highlighted when your error occurs?

---
Regards,
Norman

"Frank & Pam Hayes" wrote in message
news:V7i1f.10254$Ll2.5073@trnddc04...
This solution works fine in a regular workbook, but when I save it as an
XLA add-in file I get an error of the Worksheets Object. Any ideas?

Frank



"Frank & Pam Hayes" wrote in message
news:oE51f.13366$Vt3.12370@trnddc01...
I have created an Excel Add-In that formerly displayed the add-in's
version and date in a message box based on values in one of the add-ins
worksheets using the following (where A1 contained the version number and
A2 contained the date last updated):

Sub DisplayVersion

Msgbox "Version "&Worksheets("Sheet1").Range("A1")& _
" updated on "&Worksheets("Sheet1").Range("A2")

End Sub

I am trying to move this function to a UserForm but can not figure out
how to get data from a worksheet into a string of text in a label within
the UserForm. Can someone point me to an example?

Thank you,

Frank Hayes

Hi Frank,

I regret to say that I have been unable to reproduce your problem.

To replicate the relevant elements of your scenario, I created an addin with
a useform. I added the previously suggested UserForm_Initialize code. In a
standard module, I added your AboutThisForm code. I attached a toolbar to
the addin and assigned the AboutThisForm macro to a toolbar button.

The code ran without problem when invoked by the button.

Two inccidental points:

I assume that the dot in
Unload About.This
is a typo.


In the form's initialize code, I changed

With Worksheets("Sheet1")
to
With Thisworkbook.Sheets("Sheet1")

---
Regards,
Norman



"Frank & Pam Hayes" wrote in message
news:h4j1f.6071$Tz2.1232@trnddc02...
Norman,

My form name is AboutThis. I am calling it with the following
subroutine:

Sub AboutThisForm()
AboutThis.Show
Unload About.This
End Sub

This code is placed in a module of the XLA file and is called via a custom
toolbar button that executes the AboutThisForm macro.

When the error occurs, the AboutThis.Show line is highlighted.

The exact error reads: Run Time Error '9': Subscript out of range.

Thanks,

Frank


"Norman Jones" wrote in message
...
Hi Frank,

What code are you using to display the userform and where is that code
placed?

What line of code is highlighted when your error occurs?

---
Regards,
Norman

"Frank & Pam Hayes" wrote in message
news:V7i1f.10254$Ll2.5073@trnddc04...
This solution works fine in a regular workbook, but when I save it as an
XLA add-in file I get an error of the Worksheets Object. Any ideas?

Frank



"Frank & Pam Hayes" wrote in message
news:oE51f.13366$Vt3.12370@trnddc01...
I have created an Excel Add-In that formerly displayed the add-in's
version and date in a message box based on values in one of the add-ins
worksheets using the following (where A1 contained the version number
and A2 contained the date last updated):

Sub DisplayVersion

Msgbox "Version "&Worksheets("Sheet1").Range("A1")& _
" updated on "&Worksheets("Sheet1").Range("A2")

End Sub

I am trying to move this function to a UserForm but can not figure out
how to get data from a worksheet into a string of text in a label
within the UserForm. Can someone point me to an example?

Thank you,

Frank Hayes

Norman ... you fixed my problem! Changing

With Worksheets("Sheet1")
to
With Thisworkbook.Sheets("Sheet1")

corrected the error I was getting. I think it is going to be a long time
until I have mastered the nuances of VBA.

I truely appreciate all the time and effort you put into troubleshooting my
code. Thank you.

Frank

"Norman Jones" wrote in message
...
Hi Frank,

I regret to say that I have been unable to reproduce your problem.

To replicate the relevant elements of your scenario, I created an addin
with a useform. I added the previously suggested UserForm_Initialize code.
In a standard module, I added your AboutThisForm code. I attached a
toolbar to the addin and assigned the AboutThisForm macro to a toolbar
button.

The code ran without problem when invoked by the button.

Two inccidental points:

I assume that the dot in
Unload About.This
is a typo.


In the form's initialize code, I changed

With Worksheets("Sheet1")
to
With Thisworkbook.Sheets("Sheet1")

---
Regards,
Norman



"Frank & Pam Hayes" wrote in message
news:h4j1f.6071$Tz2.1232@trnddc02...
Norman,

My form name is AboutThis. I am calling it with the following
subroutine:

Sub AboutThisForm()
AboutThis.Show
Unload About.This
End Sub

This code is placed in a module of the XLA file and is called via a
custom toolbar button that executes the AboutThisForm macro.

When the error occurs, the AboutThis.Show line is highlighted.

The exact error reads: Run Time Error '9': Subscript out of range.

Thanks,

Frank


"Norman Jones" wrote in message
...
Hi Frank,

What code are you using to display the userform and where is that code
placed?

What line of code is highlighted when your error occurs?

---
Regards,
Norman

"Frank & Pam Hayes" wrote in message
news:V7i1f.10254$Ll2.5073@trnddc04...
This solution works fine in a regular workbook, but when I save it as
an XLA add-in file I get an error of the Worksheets Object. Any
ideas?

Frank



"Frank & Pam Hayes" wrote in message
news:oE51f.13366$Vt3.12370@trnddc01...
I have created an Excel Add-In that formerly displayed the add-in's
version and date in a message box based on values in one of the add-ins
worksheets using the following (where A1 contained the version number
and A2 contained the date last updated):

Sub DisplayVersion

Msgbox "Version "&Worksheets("Sheet1").Range("A1")& _
" updated on "&Worksheets("Sheet1").Range("A2")

End Sub

I am trying to move this function to a UserForm but can not figure
out how to get data from a worksheet into a string of text in a label
within the UserForm. Can someone point me to an example?

Thank you,

Frank Hayes