Displaying Cell Value in UserForm Label

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" <[email protected]> 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" <[email protected]> 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" <[email protected]> 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" <[email protected]> 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" <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> 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" <[email protected]> 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" <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> 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" <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> 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" <[email protected]> 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
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>