userform
 

hello everybody. I have a question: I need a userform to have a cell or a
text box or whatever in which I can see the content of a cell in a workbook
(i.e. =sheet!2 e10). is it possible?

userform
 

Hi,

Yes it's possible.

Sub ShowTheForm()
frmViewCells.Show vbModeless
End Sub

Private Sub cmdOK_Click()
Dim S2 As Worksheet
Set S2 = Sheets(2)
Me.txtNo1.Value = S2.Cells(10, 5).Value 'Col E, Row 10
End Sub

You could be on Sheet1 looking at a user form and viewing
different cells on Sheet2 as I have shown. This just
shows a simple example. Is that what you were trying to
do?

-----Original Message-----
hello everybody. I have a question: I need a userform to
have a cell or a
text box or whatever in which I can see the content of a
cell in a workbook
(i.e. =sheet!2 e10). is it possible?


.

userform
 

Hello Antonov,

The simplest way to do it with code is:
TextBox1.Text = ThisWorkbook.Worksheets("name").Range("A1").Formul a


U¿ytkownik "Antonov" napisa³ w wiadomo¶ci
...
hello everybody. I have a question: I need a userform to have a cell or a
text box or whatever in which I can see the content of a cell in a
workbook
(i.e. =sheet!2 e10). is it possible?

userform
 

Hi there Losmac
where do I have to input the code? I have tried it but nothing happened
"losmac" wrote in message
...
Hello Antonov,

The simplest way to do it with code is:
TextBox1.Text = ThisWorkbook.Worksheets("name").Range("A1").Formul a


U¿ytkownik "Antonov" napisa³ w wiadomo¶ci
...
hello everybody. I have a question: I need a userform to have a cell or
a
text box or whatever in which I can see the content of a cell in a
workbook
(i.e. =sheet!2 e10). is it possible?

userform
 

Antonov,

You input the code "behind" the cmdOK button. This is how
you do it: First make the button on the form. Name it
cmdOK (or whatever you would like) in the properties
section. Write the caption as "Enter" also in the
properties section.

Now double click this newly created button. You should
see this:

Private Sub cmdOK_Click()

End Sub

Now put Losmac's code or my code in there. Both should
work. I hope that helps.

Losmac's code is a little different. The text box in this
case, is named "TextBox1", instead of "txtNo1". He's
referring to a sheet called "name" (rather than the second
sheet "Sheet(2)") and a cell at the top-left (rather than
at row 10, column E). Also it's written for a "formula"
(rather than just a "value").

For fun, you could put both codes in this subroutine like
this, with two Text boxes on your form. One text box
would be called "TextBox1", and the other one would be
called "txtNo1". Make sure one of your Excel workbook
worksheets is called "name". This would be the code:

Private Sub cmdOK_Click()
Dim tw As Workbook
Dim S2 As Worksheet
Dim NM As Worksheet

Set tw = ThisWorkbook
Set S2 = tw.Sheets(2)
Set NM = tw.Sheets("name")

S2.Cells(10, 5).Value = Me.txtNo1.Value
NM.Range("A1").Formula = Me.TextBox1.Text
End Sub

Add a "cmdExit" button to your form. Double click this
newly created exit button and put in this code:

Private Sub cmdExit_Click()
End
End Sub

Or you could write:

Private Sub cmdExit_Click()
Unload Me
End Sub

Rick

-----Original Message-----
Hi there Losmac
where do I have to input the code? I have tried it but
nothing happened
"losmac" wrote in message
...
Hello Antonov,

The simplest way to do it with code is:
TextBox1.Text = ThisWorkbook.Worksheets("name").Range
("A1").Formula


U¿ytkownik "Antonov" napisa³ w
wiadomo¶ci
...
hello everybody. I have a question: I need a userform
to have a cell or
a
text box or whatever in which I can see the content
of a cell in a
workbook
(i.e. =sheet!2 e10). is it possible?






.

userform
 

Hello Rick, sorry to bother you again but here you see what the
CommandButton has to do already. I've tried to squeeze your formula in this
but it didn't work.
You are right when you say that I need the Userform to input the data. The
sheet which should give me the answer to put in the TextBox4 is LOADSHEET
and the cell is I53. I understood the formula you wrote but I don't know
where to fit it in the formula's below.


Private Sub CommandButton1_Click()
[a1].Activate
findvalue = UCase(ComboBox4.Value)
If Not ActiveSheet.UsedRange.Find(findvalue, lookat:=xlWhole,
matchcase:=True) Is Nothing Then
ActiveSheet.UsedRange.Find(findvalue, lookat:=xlWhole,
matchcase:=True).Activate
Else
MsgBox "The Position could not be found"
Exit Sub
End If
With ActiveCell
If Right(findvalue, 1) = "L" Then
If .Offset(-1, 0).Value = "" Then
If ActiveSheet.UsedRange.Find(ComboBox2.Value, lookat:=xlWhole)
Is Nothing Then
.Offset(-2, 0) = ComboBox2.Value
Else
MsgBox "Pallet already in use"
Exit Sub
End If
.Offset(-4, 0) = ComboBox1.Value
.Offset(-3, 0) = TextBox1.Value
.Offset(-1, 0) = TextBox2.Value
Else
MsgBox "Position is already taken"
Exit Sub
End If
Else
If .Offset(1, 0).Value = "" Then
If ActiveSheet.UsedRange.Find(ComboBox2.Value, lookat:=xlWhole)
Is Nothing Then
.Offset(3, 0) = ComboBox2.Value
Else
MsgBox "Pallet already in use"
Exit Sub
End If
.Offset(1, 0) = ComboBox1.Value
.Offset(2, 0) = TextBox1.Value
.Offset(3, 0) = ComboBox2.Value
.Offset(4, 0) = TextBox2.Value
Else
MsgBox "Position is already taken"
Exit Sub
End If
End If
End With
TextBox1.Value = ""
ComboBox1.Value = ""
TextBox2.Value = ""
ComboBox2.Value = ""
ComboBox3.Value = ""
ComboBox4.Value = ""
End Sub
"Rick" wrote in message
...
Antonov,

I think the reason it doesn't work, is because you want to
do something different. Now, I assume you want to use the
user form as a tool to help you with the data entry
(rather than just reading what the values are in a cell).
In that case, you switch around the one line of code
around. Maybe this will do what you want:

Private Sub cmdOK_Click()
Dim S2 As Worksheet
Set S2 = Sheets(2)
S2.Cells(10, 5).Value = Me.txtNo1.Value
End Sub

The way I have written it, the cmdOK button is the "enter"
button (the name is cmdOK, but the caption is "Enter").
The one text box is named "txtNo1".

After you understand what is written above, then you can
expand your userform into something more useful, like:

Private Sub cmdOK_Click()
Dim S2 As Worksheet
Set S2 = Sheets(2)
S2.Cells(10, 5).Value = Me.txtNo1.Value
S2.Cells(11, 5).Value = Me.txtNo2.Value
S2.Cells(12, 5).Value = Me.txtNo3.Value
End Sub

With three text boxes you can make three entries all at
the same time into three different cells, right below each
other. I hope that helps.

I'm using the Cells Method. Other programmers might give
you some different way of writing this with ranges. But
the Cells Method is an easy way for someone to understand,
when they are first starting out. I like looping through
the cells too...and that is my preferred method...but
everyone has their own favorite way. :)

-----Original Message-----
Hello Rick.
thanks for your reply. This is what I am trying to do:
when I am in a sheet I open a userform which I use to
input data. Everytime
I press an "Enter" button in the userform the data
influences a result in
sheet 2 (which has a different name, but for the
ease....).
I've tried what you sent but with no result.


"Rick" wrote in message
...
Hi,

Yes it's possible.

Sub ShowTheForm()
frmViewCells.Show vbModeless
End Sub

Private Sub cmdOK_Click()
Dim S2 As Worksheet
Set S2 = Sheets(2)
Me.txtNo1.Value = S2.Cells(10, 5).Value 'Col E, Row
10
End Sub

You could be on Sheet1 looking at a user form and
viewing
different cells on Sheet2 as I have shown. This just
shows a simple example. Is that what you were trying to
do?

-----Original Message-----
hello everybody. I have a question: I need a userform
to
have a cell or a
text box or whatever in which I can see the content of
a
cell in a workbook
(i.e. =sheet!2 e10). is it possible?


.



.

userform
 

Antonov,

I noticed that it appears that you are using these boxes:

TextBox1
TextBox2
ComboBox1
ComboBox2
ComboBox4

I did not see where the values were entered or used from
ComboBox3, other than at the very bottom.

The variable "findvalue" is identified in ComboBox4.
Where is TextBox4 in the subroutine below? How does it
tie in?

To clarify this, what I would do is write it in "pseudo"
code. It might help you understand it better too. Just
write it out in plain English like this:

For example:

If the last character of my variable is "L" Then
(Do this....)
Else If
(Do that....)
End If

If you write out all the logic that way for the whole
subroutine (without knowing the technical syntax), it
would help explain it more clearly. It might help you as
well. I have trouble following what you want precisely.
You might want to resubmit this to the newsgroup, with
some of my suggestions.

Please be persistent (that is, no need to apologize for
bothering me....) You'll finally figure it out. I'm sure
someone will catch on to what you want, and you'll find a
solution. A lot of nice people have helped me with some
complex solutions, with problems that I've had. I'm glad
to help you too.

-----Original Message-----
Hello Rick, sorry to bother you again but here you see
what the
CommandButton has to do already. I've tried to squeeze
your formula in this
but it didn't work.
You are right when you say that I need the Userform to
input the data. The
sheet which should give me the answer to put in the
TextBox4 is LOADSHEET
and the cell is I53. I understood the formula you wrote
but I don't know
where to fit it in the formula's below.


Private Sub CommandButton1_Click()
[a1].Activate
findvalue = UCase(ComboBox4.Value)
If Not ActiveSheet.UsedRange.Find(findvalue,
lookat:=xlWhole,
matchcase:=True) Is Nothing Then
ActiveSheet.UsedRange.Find(findvalue, lookat:=xlWhole,
matchcase:=True).Activate
Else
MsgBox "The Position could not be found"
Exit Sub
End If
With ActiveCell
If Right(findvalue, 1) = "L" Then
If .Offset(-1, 0).Value = "" Then
If ActiveSheet.UsedRange.Find
(ComboBox2.Value, lookat:=xlWhole)
Is Nothing Then
.Offset(-2, 0) = ComboBox2.Value
Else
MsgBox "Pallet already in use"
Exit Sub
End If
.Offset(-4, 0) = ComboBox1.Value
.Offset(-3, 0) = TextBox1.Value
.Offset(-1, 0) = TextBox2.Value
Else
MsgBox "Position is already taken"
Exit Sub
End If
Else
If .Offset(1, 0).Value = "" Then
If ActiveSheet.UsedRange.Find
(ComboBox2.Value, lookat:=xlWhole)
Is Nothing Then
.Offset(3, 0) = ComboBox2.Value
Else
MsgBox "Pallet already in use"
Exit Sub
End If
.Offset(1, 0) = ComboBox1.Value
.Offset(2, 0) = TextBox1.Value
.Offset(3, 0) = ComboBox2.Value
.Offset(4, 0) = TextBox2.Value
Else
MsgBox "Position is already taken"
Exit Sub
End If
End If
End With
TextBox1.Value = ""
ComboBox1.Value = ""
TextBox2.Value = ""
ComboBox2.Value = ""
ComboBox3.Value = ""
ComboBox4.Value = ""
End Sub
"Rick" wrote in message
...
Antonov,

I think the reason it doesn't work, is because you want
to
do something different. Now, I assume you want to use
the
user form as a tool to help you with the data entry
(rather than just reading what the values are in a
cell).
In that case, you switch around the one line of code
around. Maybe this will do what you want:

Private Sub cmdOK_Click()
Dim S2 As Worksheet
Set S2 = Sheets(2)
S2.Cells(10, 5).Value = Me.txtNo1.Value
End Sub

The way I have written it, the cmdOK button is
the "enter"
button (the name is cmdOK, but the caption is "Enter").
The one text box is named "txtNo1".

After you understand what is written above, then you can
expand your userform into something more useful, like:

Private Sub cmdOK_Click()
Dim S2 As Worksheet
Set S2 = Sheets(2)
S2.Cells(10, 5).Value = Me.txtNo1.Value
S2.Cells(11, 5).Value = Me.txtNo2.Value
S2.Cells(12, 5).Value = Me.txtNo3.Value
End Sub

With three text boxes you can make three entries all at
the same time into three different cells, right below
each
other. I hope that helps.

I'm using the Cells Method. Other programmers might
give
you some different way of writing this with ranges. But
the Cells Method is an easy way for someone to
understand,
when they are first starting out. I like looping
through
the cells too...and that is my preferred method...but
everyone has their own favorite way. :)

-----Original Message-----
Hello Rick.
thanks for your reply. This is what I am trying to do:
when I am in a sheet I open a userform which I use to
input data. Everytime
I press an "Enter" button in the userform the data
influences a result in
sheet 2 (which has a different name, but for the
ease....).
I've tried what you sent but with no result.


"Rick" wrote in message
...
Hi,

Yes it's possible.

Sub ShowTheForm()
frmViewCells.Show vbModeless
End Sub

Private Sub cmdOK_Click()
Dim S2 As Worksheet
Set S2 = Sheets(2)
Me.txtNo1.Value = S2.Cells(10, 5).Value 'Col E,
Row
10
End Sub

You could be on Sheet1 looking at a user form and
viewing
different cells on Sheet2 as I have shown. This just
shows a simple example. Is that what you were
trying to
do?

-----Original Message-----
hello everybody. I have a question: I need a
userform
to
have a cell or a
text box or whatever in which I can see the content
of
a
cell in a workbook
(i.e. =sheet!2 e10). is it possible?


.



.



.