Userform to show some values on a sheet

flightjock

New Member
Joined
Mar 18, 2002
Messages
48
I have a workbook consisting of 12 sheets. On every sheet there are some statistics in certain cells, always in the same range (say column BE) but out of sight due to the size of the sheets. I would like to have a userform that shows these statistics by clicking a macrobutton. Each workbook sheet would get such a macro button and the userform should show the statistics of the particular sheet where the button was pressed. I know how to make a new userform but I can't figure out how to link the controls to the active worksheet. Any help would be appreciated.
Thanks,
Anthony
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Hi Anthony

you can get the name of the activesheet with this code :
activesheet.name

now make some public sub in a module that use this info, lets say :

sub GetStatistics (SheetName As String)
worksheet(SheetName).activate
text1.text = activesheet.cells(1,58).value
' now you can get the values of the
' columns of the sheet where the button
' was pressed and show it on the form in
' textfield text1
form.show
end sub

on the sheet you call this functions by
call getstatistics(activesheet.name)

greets

koen
 
Upvote 0
You don't say how you want to display the stats.

On the form in a listbox? If so you can use the rowsource property BE:BE for the whole column, or BE1:BE20 if you know how many cells you need to include, which is better.

You can also use a range in the list property, either can be set at runtime to count the number of cells in the column
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top