Need to display results of any cell when selected in a form?

**T-Rex19** · 06-05-2012, 10:03 AM

I have wasted two whole days searching the net for a seemingly simple solution, but I am not finding anything that I can even combine. PLEASE HELP, my deadline is tomorrow, June 6, 2012. The spreadsheet has to be locked for editing and there are cells that contain explanation of variances. When a user/reviewer selects any cell or double clicks, I would like the results to be shown in a form so that they can see them (they are lengthy). I also don't want them to be able to close the form. If I have to expand rows, the spreadsheet gets to be too cumbersome to move around. The goal is to make it as easy for the user/reviewer as possible. Note: Using the text box and linking the cell will not work, because I need it to be flexible for all cells when selected to see the results. I cannot copy/paste/special because the results are based on a decision tree that changes based on values. VBA code would be an option. Any other suggestions that I am not thinking of would be greatly appreciated.
I need better code to not allow the user to close the the UserForm1 and that it would display at the top of the spreadsheet as any cell is being selected.

**T-Rex19** · 06-06-2012, 10:22 AM

So the following code pops up the address not the cell results and I know I have Address indicated. What should I change it to? Also, it opens on a regular click and a right click. I just want right click only.

SHEET CODEOption Explicit

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Target.Cells.Count > 1 Then Exit Sub
Cancel = True
FormPartial.Show
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
FormPartial.Show
End Sub

FORM CODEPrivate Sub CommandButton1_Click()
addy = TextBox1.Value
Range(addy).FormulaR1C1 = TextBox2.Value
Unload Me
End Sub

Private Sub UserForm_initialize()
Me.TextBox1.Value = ActiveCell.Address
End Sub

**T-Rex19** · 06-06-2012, 12:30 PM

Solution
Option Explicit

Private Sub Worksheet_BeforeRightClick(ByVal Target As Excel.Range, ByRef Cancel As Boolean)
If Target.Cells.Count > 1 Then Exit Sub
Cancel = True
FormPartial.Show
End Sub

Private Sub CommandButton1_Click()
addy = TextBox1.Value
Range(addy).FormulaR1C1 = TextBox2.Value
Unload Me
End Sub

Private Sub UserForm_initialize()
Me.TextBox1.Value = ActiveCell.Value
End Sub