PDA

View Full Version : Solved: Transfer variable to form



NumberCrunch
03-15-2013, 02:55 AM
Hi,

I am having a process that starts when I'm double clicking a cell. It used to work perfectly when the process was in a standard module in a sub that was called from a worksheet module:

worksheet:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Call AddInfo(Target)
End Sub

Module:
Sub AddInfo(Target As Range)
[process]
End Sub

However, now I want the process to be in a form code. But I need to carry the target range properties, so I know the target cell.

There is 1 solution that I have, but it seems for me just too "unprofessional", if You know what I mean. It would be to put the range properties (row number and column number) in a specific place in the workbook, that I would read again once the form is launched and then clear the cells.
I'm trying to keep the code clean and elegant :). Any ideas how to transfer range variable to form?

NumberCrunch
03-15-2013, 03:36 AM
I believe this issue has been discussed before and it is among tricky techniques. But I still have not found a good solution to this problem.

I know that You can add information from a standard module to the Form elements before actually showing the form, such as:

MyForm.TextBox1.Text = MyText
and then:
MyForm.Show

but can You transfer variables from the standard module to form module to work with? Can You transfer an array of data, for example?

Teeroy
03-15-2013, 05:34 AM
You could use Public variables. See the following link for a discussion http://www.vbaexpress.com/forum/showthread.php?t=29025.

snb
03-15-2013, 07:44 AM
Or you can define it as a named range.