Text to Form Textbox and back

[Pat]

I am trying to send the current value of a cell to a
textbox on a form. I ultimately would like the user to
then edit the data and upon pressing an "ok" button to
send the updated information back to the cell.

Thus far I have:
txtA = ActiveSheet.Cells(Count, 1).Value
txtB = ActiveSheet.Cells(Count, 1).Offset(0, 1).Value
txtC = ActiveSheet.Cells(Count, 15).Value

frmForm.Show

The text does not appear in the textboxes on the form when
it loads, but I know it is assigning the value. How do I
make it appear on the form? How do I then get it back to
my spreadsheet?

Thanks

Pat

 

[Bob Phillips]

Pat,

Userforms or worksheet form controls?

Where is that code that loads the textboxes, and when is it run?

Have you tried setting the ControlSource (userform ) or cell link (worksheet
form control) property?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Pat]

Bob
Currently when a toolbar button is clicked an input box
will appear as the user to input a name. The program
searches the excel list for the name and brings up a form
over the spreadsheet. On the form I want the name and
some other relevent information from that line to appear
so that it can be altered. This code is part of the what
happens when the toolbar button is clicked.

Below is what I have so far.

Thanks

Pat


Sub EditEmployeeInfo()
Dim Name As String
Dim NameUpdate As String
Dim InitialsUpdate As String
Dim StartTotUpdate As String
Dim Found As Boolean
Dim Count As Long

ActiveSheet.Unprotect

Name = InputBox("Enter the name of the employee to be
changed.", , "")

If Name = "" Then
MsgBox "Error. No Data Entered."
Exit Sub
End If

'Find EditEmp Name in Text and Set values in line equal
to " "
Count = 0
Do While Count < 60
Count = Count + 1
If ActiveSheet.Cells(Count, 1) = Name Then
Found = True
ActiveSheet.Cells(Count, 1).Activate

txtEmpName = ActiveSheet.Cells(Count, 1).Value
txtStartTot = ActiveSheet.Cells(Count, 1).Offset
(0, 1).Value
TxtInitials = ActiveSheet.Cells(Count, 15).Value

frmEditEmpInfo.Show

'This is why I know it is assigning the value
correctly. This was just for me to be able to check.
ActiveSheet.Cells(Count, 20) = txtEmpName


End If
Loop
If Found = False Then
MsgBox "Name Not Found"
End If

Load frmEditEmpInfo

End Sub

 

[Bob Phillips]

Pat,

What is happening is that by not qualifying the textboxes with their
container class (frmEditEmpInfo), you are not referring to the textboxes on
the form, but rather are creating new variables in your module with those
names. If found it very easily as I have the Option Explicit statement at
the start of all of my modules, so I got a compile error saying these
variables did not exits.

I suggest you start using Option Explicit as it is a very useful debugging
aid, and to get around this problem, change these lines

txtEmpName = ActiveSheet.Cells(Count, 1).Value
txtStartTot = ActiveSheet.Cells(Count, 1).Offset (0, 1).Value
TxtInitials = ActiveSheet.Cells(Count, 15).Value

frmEditEmpInfo.Show


to this

With ActiveSheet
frmEditEmpInfo.txtEmpName = .Cells(count, 1).Value
frmEditEmpInfo.txtStartTot = .Cells(count, 1).Offset(0, 1).Value
frmEditEmpInfo.txtInitials = .Cells(count, 15).Value
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Pat]

Bob,

What do you mean by Option Explicit? I am sorry. I am
still very new to VBA and trying to learn as I go.
Unfortunately the VBA Help only gets me so far and the
book I have seems to discuss things that I can't find.

The form now appears with the proper text, now I am trying
to get the text back to the spreadsheet. I set up my "Ok"
button, but I don't think I am calling it properly because
it doesn't do anything. I orginally put the button the
form I made. Now I am trying to do a Click Sub procedure
and it is not doing anything.

Ideas?

Thank you very much

Pat

 

[Bob Phillips]

Pat,

Option Explicit is a statement that you put right at the start of a code
module, and it forces you to declare all variables before using them. This
ensures that you don't accidentally use the wrong name when addressing a
variable. If you do use a variable without declaring it, you get a compile
error.

On the second part, did you put the button click code in the form code
module?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)