userform

  • Thread starter Thread starter Antonov
  • Start date Start date
A

Antonov

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?
 
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?
 
Hello Antonov,

The simplest way to do it with code is:
TextBox1.Text = ThisWorkbook.Worksheets("name").Range("A1").Formula
 
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
 
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
 
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
Click to expand...
(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
Click to expand...
(ComboBox2.Value, lookat:=xlWhole)
 
Back
Top