magical floating table of death

BlueSocksMcGee

New Member
Joined
May 24, 2006
Messages
39
I am stumped! Please help!
How does one setup a seperate form or text box or window that displays atable of data linked to a range of cells, where if you change the numbers in the floating table the numbers in the range change, and vice versa (hence the "magic"). As someone navigates the worksheet, the form/textbox/window follows the user around (hence the "floating" part)

I can't seem to figure out how to do it!!!! (hence the "of death")
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
You will need to have a Userform for this.
WorkSheet_Change event updates the userform
Userform Textbox_Change event updates the cell(s) or set the ControlSource property of the textbox.

To move the Userform around, set the Top and Left properties of the form using Top & Left property of a cell. This needs adjustment of Top property because Pixel 0,0 for a Userform is top left of *Window* ,
* 0,0 of Cells is top left of cell A1*.

PICTURE OF CELLS
It is possible to have a Picture that changes with cell values, but this is only 1-way i.e. changing cell changes picture. Cannot change picture.

Copy range of cells.
Select cell to paste
Hold Shift key/select menu Edit
Paste Picture Link.
 
Upvote 0
Thanks Brian, I was thinking of something similar with the picture of cells. But clearly that will not allow a user to change the values in the form... any other ideas how to insert a table of data into the form? Why is this so hard?
 
Upvote 0
Brian is correct in that you will need to have a userform, but you can have each cell linked to a text box on the userform and linked to an individual cell using the control source value for that textbox.

Set your userform behaviour to ShowModal = False, this will make it "Float" for you.

A bit long winded as you will have to link each cell to an individual text box, but I have just set a quick one up on my PC and it lets me change the value in either the cell or in the floating userform and amends instantly in the other one.
 
Upvote 0
any other ideas how to insert a table of data into the form?
Use a spreadshheet control on a modeless userform. The below steps were tested and work for me.

Event code in the userform module:

Code:
Private Sub Spreadsheet1_SheetChange(ByVal Sh As OWC11.Worksheet, ByVal Target As OWC11.Range)
Application.EnableEvents = False
Range(Target.Address).Value = Target.Value
Application.EnableEvents = True
End Sub


and the initialization code (you did not specify the range of interest so for demo purposes, A1 current region):

Code:
Private Sub UserForm_Initialize()
Set rng = Range("A1").CurrentRegion
For iRow = 1 To rng.Rows.Count
For iCol = 1 To rng.Columns.Count
Spreadsheet1.Cells(iRow, iCol).value = rng.Cells(iRow, iCol).value
Next iCol
Next iRow
End Sub


This in the worksheet module:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Set rng = Range("A1").CurrentRegion
For iRow = 1 To rng.Rows.Count
For iCol = 1 To rng.Columns.Count
UserForm1.Spreadsheet1.Cells(iRow, iCol).Value = rng.Cells(iRow, iCol).Value
Next iCol
Next iRow
Application.EnableEvents = True
End Sub



Call the userform in a macro that specifies modeless = False (0), in a standard module, which achieves the "floating" effect:

Code:
Option Explicit
Public rng As Range
Public iRow&
Public iCol&

Sub ShowForm()
UserForm1.Show 0
End Sub


Finally, spreadsheet controls elicit an activex prompt when the workbook opens, so if you get one (you'll know it when you see it) and it annoys you, this link will help:

http://support.microsoft.com/default.aspx?scid=kb;en-us;827742.
 
Upvote 0
Thanks Urtis for guidance

Can u please guide me step by step how to create form and insert ur code.

This is new for me.

Regards
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

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