Userform Cell Link

[Swift2003]

Im tryin 2 create a userform that links a textbox with a cell referenc
on a worksheet. when the userform is started the textbox should retur
the numeric value from the cell but change the cell value when the use
change the value in the textbox. i think it should be quite simple bu
i'm lost

 

[gitcypher]

Did you create the user form from within Visual basic, or "insert
Microsoft Excel 5.0 dialog" from within the workbook?

-Gitcyphe

 

[gitcypher]

Check this spreadsheet out. It may help

Attachment filename: dialogtest.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=48657

 

[Swift2003]

I've created the userform from Visual Basic editor within excel

 

[gitcypher]

Unfortunately, I'm quite unfamiliar with creating user forms using tha
method. As you can see in the spreadsheet I attached to th
previous thread, I'm accustomed to inserting dialogsheets. If you'
like to try that method, I'm here if you have any questions.

-gitcyphe

 

[Swift2003]

using the dialogsheets how do i format text styles,size, text alignmen
etc of edit boxes?

 

[Dick Kusleika]

Swift2003

You can use the events behind the userform to link to a cell. For instance,
I would use the Initialize event to put the value in the textbox. Then use
the AfterUpdate event of the Textbox to write the value back to the cell.
If the cell is A1, then those subs might look like this

Private Sub TextBox1_AfterUpdate()

Sheet1.Range("A1").Value = Me.TextBox1.Text

End Sub


Private Sub UserForm_Initialize()

Me.TextBox1.Text = Sheet1.Range("A1").Value

End Sub

 

[gitcypher]

There's your drawback. Using dialog sheets bases the text styles etc. o
the windows interface, in specific, the interface of the end user's
So, the same dialog sheet will look like each person's os.
I believe there are pros and cons to it. Personally, I prefer having i
appear like the user's os so it makes for more of a 'user-friendly
interface. At the same time, if you make an edit box too small, and th
end user's visual settings are set to 'high contrast & extremel
large', you'll run into problems.
If you're going to be the only one using this, or know that it will b
used in a relatively controlled setting, such as multiple workstation
at your job, you won't have that problem.

-gitcyphe