hello everyone...
i have a few questions about textboxes and the answer is i suspect simple...
--by code i know the answer but it´s the only option i have for this?
1) only let the user to insert numbers in a textbox (without code isnumeric)
2) the textbox must be formated with 2 decimals (without format(....,"0,00"))
3) 2 textboxes must have the same value so if i input in one of them let´s
say the number 5 the other must list 5(without the change event code)
thanks for any help
Miguel
1)
Private Sub ZipCode_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer )
With Me.Textbox1
Select Case True
Case (KeyCode >= 96 And KeyCode < 105) 'numeric keypad
'exit quietly
Case (KeyCode >= 48 And KeyCode < 57) 'normal keypad
'exit quietly
Case KeyCode = 8 'backspace
If Len(.Text) > 0 Then
.Text = Left(.Text, Len(.Text) - 1)
End If
KeyCode = 0
Case KeyCode = 46 ' decimal point
Case Else
KeyCode = 0
Beep
End Select
End With
End Sub
2)
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim iPos As Long
With Me.TextBox1
iPos = InStr(1, .Text, ".")
If iPos > 0 Then
If iPos <> Len(.Text) - 2 Then
MsgBox "Invalid amount"
Cancel = True
End If
End If
End With
End Sub
3) What do you mean by '... without the change event code ...'? Why not just
set it to the other textbox value?
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Liedson31" <[email protected]> wrote in message
news:[email protected]...
> hello everyone...
> i have a few questions about textboxes and the answer is i suspect
simple...
> --by code i know the answer but it´s the only option i have for this?
> 1) only let the user to insert numbers in a textbox (without code
isnumeric)
> 2) the textbox must be formated with 2 decimals (without
format(....,"0,00"))
> 3) 2 textboxes must have the same value so if i input in one of them let´s
> say the number 5 the other must list 5(without the change event code)
>
>
> thanks for any help
> Miguel
thanks again Bob:
if you remember my previous problem(1.500 textboxes)) ),let´s say,input
this code into all the textboxes will be a nightmare !!! but it´s the only
solution isn´t it?
what i was askinhg, if is there other solution like format the textbox in
the form open,is there a format property of textbox(like cells format of
excel)...i don´t think so to
put this code in all the textboxes .....
the 3) question mean this:
i have a textbox1 and a textbox2...what i want is when i put a value in
textbox1 the textbox2 must have the same value and either to textbox2
input.....
what i want is something like textbox2=textbox1 but the only way o know is
the
Private Sub.... Change()
textbox2=textbox1
end sub
is there a way of making this without write code,i tried in the
controlsource but i can´t do it..in other hand i want to make something like
this to
textbox1,textbox2,textbox3,this textboxes are all in a frame..... and i want
to have a textbox(n) that is the sum of all of them. but in a frame only can
do this with the change event, so if i have 2 frames when i jump into frame2
textbox(n) takes the sum value of all,but if i jump from textbox1 to textbox2
the textbox(n) does´nt take the sum value............
can you help with this 2 problems.....and once more thank you very much Bob
"Bob Phillips" wrote:
> 1)
>
> Private Sub ZipCode_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
> ByVal Shift As Integer )
> With Me.Textbox1
> Select Case True
> Case (KeyCode >= 96 And KeyCode < 105) 'numeric keypad
> 'exit quietly
> Case (KeyCode >= 48 And KeyCode < 57) 'normal keypad
> 'exit quietly
> Case KeyCode = 8 'backspace
> If Len(.Text) > 0 Then
> .Text = Left(.Text, Len(.Text) - 1)
> End If
> KeyCode = 0
> Case KeyCode = 46 ' decimal point
> Case Else
> KeyCode = 0
> Beep
> End Select
> End With
> End Sub
>
> 2)
>
> Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
> Dim iPos As Long
> With Me.TextBox1
> iPos = InStr(1, .Text, ".")
> If iPos > 0 Then
> If iPos <> Len(.Text) - 2 Then
> MsgBox "Invalid amount"
> Cancel = True
> End If
> End If
> End With
> End Sub
>
> 3) What do you mean by '... without the change event code ...'? Why not just
> set it to the other textbox value?
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Liedson31" <[email protected]> wrote in message
> news:[email protected]...
> > hello everyone...
> > i have a few questions about textboxes and the answer is i suspect
> simple...
> > --by code i know the answer but it´s the only option i have for this?
> > 1) only let the user to insert numbers in a textbox (without code
> isnumeric)
> > 2) the textbox must be formated with 2 decimals (without
> format(....,"0,00"))
> > 3) 2 textboxes must have the same value so if i input in one of them let´s
> > say the number 5 the other must list 5(without the change event code)
> >
> >
> > thanks for any help
> > Miguel
>
>
>
This methodology can be adapted to textboxes:
http://j-walk.com/ss/excel/tips/tip44.htm
None of what you want to do is supported without using events and code.
You can only link to a cell in a sheet to the best of my knowledge. If you
did that and then linked a cell containing a sum formula to the sum textbox,
the formula would get overwritten when the value in the cell changed.
I still can't imagine a situation where you would need 1500 textboxes all
visible at the same time - and if that isn't the requirement (that the be
visible all at the same time), then there is no requirement for 1500 actual
textboxes. You really need to be more creative in your thinking.
for example, If I had 5 textboxes and 5 labels
iflag = 3
for i = 1 to 5
userform1.Controls("Label" & i).Caption = "Item" & iflag*5 + i
userform1.controls("Textbox" & i).Value = ""
Next
If I wanted to show the form as having the labels for virtual textboxes 1
to 5 I set iflag to 0
for 6 to 10 set iflag to 1
for 11 to 15 set iflag to 2
for 16 to 20 set iflag to 3
Obviously this requires a bit more code and adds some complexity, but that
complexity is trivial to what you are trying to do. With the above concept,
I could represent 10, 1500, 3000 virtual textboxes with minimal additional
work. I understand the labels captions wouldn't be item1 to item1500, but
you can always have an array with the 1500 unique captions and index into
the array.
--
Regards,
Tom Ogilvy
"Liedson31" <[email protected]> wrote in message
news:[email protected]...
> thanks again Bob:
> if you remember my previous problem(1.500 textboxes
> this code into all the textboxes will be a nightmare !!! but it´s the only
> solution isn´t it?
> what i was askinhg, if is there other solution like format the textbox in
> the form open,is there a format property of textbox(like cells format of
> excel)...i don´t think so to
to
> put this code in all the textboxes .....
>
> the 3) question mean this:
> i have a textbox1 and a textbox2...what i want is when i put a value in
> textbox1 the textbox2 must have the same value and either to textbox2
> input.....
> what i want is something like textbox2=textbox1 but the only way o know is
> the
> Private Sub.... Change()
> textbox2=textbox1
> end sub
>
> is there a way of making this without write code,i tried in the
> controlsource but i can´t do it..in other hand i want to make something
like
> this to
>
> textbox1,textbox2,textbox3,this textboxes are all in a frame..... and i
want
> to have a textbox(n) that is the sum of all of them. but in a frame only
can
> do this with the change event, so if i have 2 frames when i jump into
frame2
> textbox(n) takes the sum value of all,but if i jump from textbox1 to
textbox2
> the textbox(n) does´nt take the sum value............
>
> can you help with this 2 problems.....and once more thank you very much
Bob
>
>
> "Bob Phillips" wrote:
>
> > 1)
> >
> > Private Sub ZipCode_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
> > ByVal Shift As Integer )
> > With Me.Textbox1
> > Select Case True
> > Case (KeyCode >= 96 And KeyCode < 105) 'numeric keypad
> > 'exit quietly
> > Case (KeyCode >= 48 And KeyCode < 57) 'normal keypad
> > 'exit quietly
> > Case KeyCode = 8 'backspace
> > If Len(.Text) > 0 Then
> > .Text = Left(.Text, Len(.Text) - 1)
> > End If
> > KeyCode = 0
> > Case KeyCode = 46 ' decimal point
> > Case Else
> > KeyCode = 0
> > Beep
> > End Select
> > End With
> > End Sub
> >
> > 2)
> >
> > Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
> > Dim iPos As Long
> > With Me.TextBox1
> > iPos = InStr(1, .Text, ".")
> > If iPos > 0 Then
> > If iPos <> Len(.Text) - 2 Then
> > MsgBox "Invalid amount"
> > Cancel = True
> > End If
> > End If
> > End With
> > End Sub
> >
> > 3) What do you mean by '... without the change event code ...'? Why not
just
> > set it to the other textbox value?
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Liedson31" <[email protected]> wrote in message
> > news:[email protected]...
> > > hello everyone...
> > > i have a few questions about textboxes and the answer is i suspect
> > simple...
> > > --by code i know the answer but it´s the only option i have for this?
> > > 1) only let the user to insert numbers in a textbox (without code
> > isnumeric)
> > > 2) the textbox must be formated with 2 decimals (without
> > format(....,"0,00"))
> > > 3) 2 textboxes must have the same value so if i input in one of them
let´s
> > > say the number 5 the other must list 5(without the change event code)
> > >
> > >
> > > thanks for any help
> > > Miguel
> >
> >
> >
thanks Tom, you helped me a lot but there is a last question...sorry for
that...
how can i make the link between the textbox and the cell...
can i use the controlsource?.....i´ve tried to use it but it gives me an
error... i already use what you suggested me but i can´t update the textbox
because i can´t link it to the cell...unless i wrote textbox=cells(x,y),but i
believe there is a way of doing that without code.
thanks again
"Tom Ogilvy" wrote:
> This methodology can be adapted to textboxes:
>
> http://j-walk.com/ss/excel/tips/tip44.htm
>
> None of what you want to do is supported without using events and code.
>
> You can only link to a cell in a sheet to the best of my knowledge. If you
> did that and then linked a cell containing a sum formula to the sum textbox,
> the formula would get overwritten when the value in the cell changed.
>
> I still can't imagine a situation where you would need 1500 textboxes all
> visible at the same time - and if that isn't the requirement (that the be
> visible all at the same time), then there is no requirement for 1500 actual
> textboxes. You really need to be more creative in your thinking.
>
> for example, If I had 5 textboxes and 5 labels
>
> iflag = 3
> for i = 1 to 5
> userform1.Controls("Label" & i).Caption = "Item" & iflag*5 + i
> userform1.controls("Textbox" & i).Value = ""
> Next
>
> If I wanted to show the form as having the labels for virtual textboxes 1
> to 5 I set iflag to 0
> for 6 to 10 set iflag to 1
> for 11 to 15 set iflag to 2
> for 16 to 20 set iflag to 3
>
> Obviously this requires a bit more code and adds some complexity, but that
> complexity is trivial to what you are trying to do. With the above concept,
> I could represent 10, 1500, 3000 virtual textboxes with minimal additional
> work. I understand the labels captions wouldn't be item1 to item1500, but
> you can always have an array with the 1500 unique captions and index into
> the array.
>
>
> --
> Regards,
> Tom Ogilvy
>
> "Liedson31" <[email protected]> wrote in message
> news:[email protected]...
> > thanks again Bob:
> > if you remember my previous problem(1.500 textboxes
> > this code into all the textboxes will be a nightmare !!! but it´s the only
> > solution isn´t it?
> > what i was askinhg, if is there other solution like format the textbox in
> > the form open,is there a format property of textbox(like cells format of
> > excel)...i don´t think so to
> to
> > put this code in all the textboxes .....
> >
> > the 3) question mean this:
> > i have a textbox1 and a textbox2...what i want is when i put a value in
> > textbox1 the textbox2 must have the same value and either to textbox2
> > input.....
> > what i want is something like textbox2=textbox1 but the only way o know is
> > the
> > Private Sub.... Change()
> > textbox2=textbox1
> > end sub
> >
> > is there a way of making this without write code,i tried in the
> > controlsource but i can´t do it..in other hand i want to make something
> like
> > this to
> >
> > textbox1,textbox2,textbox3,this textboxes are all in a frame..... and i
> want
> > to have a textbox(n) that is the sum of all of them. but in a frame only
> can
> > do this with the change event, so if i have 2 frames when i jump into
> frame2
> > textbox(n) takes the sum value of all,but if i jump from textbox1 to
> textbox2
> > the textbox(n) does´nt take the sum value............
> >
> > can you help with this 2 problems.....and once more thank you very much
> Bob
> >
> >
> > "Bob Phillips" wrote:
> >
> > > 1)
> > >
> > > Private Sub ZipCode_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
> > > ByVal Shift As Integer )
> > > With Me.Textbox1
> > > Select Case True
> > > Case (KeyCode >= 96 And KeyCode < 105) 'numeric keypad
> > > 'exit quietly
> > > Case (KeyCode >= 48 And KeyCode < 57) 'normal keypad
> > > 'exit quietly
> > > Case KeyCode = 8 'backspace
> > > If Len(.Text) > 0 Then
> > > .Text = Left(.Text, Len(.Text) - 1)
> > > End If
> > > KeyCode = 0
> > > Case KeyCode = 46 ' decimal point
> > > Case Else
> > > KeyCode = 0
> > > Beep
> > > End Select
> > > End With
> > > End Sub
> > >
> > > 2)
> > >
> > > Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
> > > Dim iPos As Long
> > > With Me.TextBox1
> > > iPos = InStr(1, .Text, ".")
> > > If iPos > 0 Then
> > > If iPos <> Len(.Text) - 2 Then
> > > MsgBox "Invalid amount"
> > > Cancel = True
> > > End If
> > > End If
> > > End With
> > > End Sub
> > >
> > > 3) What do you mean by '... without the change event code ...'? Why not
> just
> > > set it to the other textbox value?
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Liedson31" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > hello everyone...
> > > > i have a few questions about textboxes and the answer is i suspect
> > > simple...
> > > > --by code i know the answer but it´s the only option i have for this?
> > > > 1) only let the user to insert numbers in a textbox (without code
> > > isnumeric)
> > > > 2) the textbox must be formated with 2 decimals (without
> > > format(....,"0,00"))
> > > > 3) 2 textboxes must have the same value so if i input in one of them
> let´s
> > > > say the number 5 the other must list 5(without the change event code)
> > > >
> > > >
> > > > thanks for any help
> > > > Miguel
> > >
> > >
> > >
>
>
>
In the controlsource property, just put the cell string, like A1. If you
want to specify a sheet, then use 'sheet name'!A1
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Liedson31" <[email protected]> wrote in message
news:[email protected]...
> thanks Tom, you helped me a lot but there is a last question...sorry for
> that...
> how can i make the link between the textbox and the cell...
> can i use the controlsource?.....i´ve tried to use it but it gives me an
> error... i already use what you suggested me but i can´t update the
textbox
> because i can´t link it to the cell...unless i wrote
textbox=cells(x,y),but i
> believe there is a way of doing that without code.
>
> thanks again
>
>
> "Tom Ogilvy" wrote:
>
> > This methodology can be adapted to textboxes:
> >
> > http://j-walk.com/ss/excel/tips/tip44.htm
> >
> > None of what you want to do is supported without using events and code.
> >
> > You can only link to a cell in a sheet to the best of my knowledge. If
you
> > did that and then linked a cell containing a sum formula to the sum
textbox,
> > the formula would get overwritten when the value in the cell changed.
> >
> > I still can't imagine a situation where you would need 1500 textboxes
all
> > visible at the same time - and if that isn't the requirement (that the
be
> > visible all at the same time), then there is no requirement for 1500
actual
> > textboxes. You really need to be more creative in your thinking.
> >
> > for example, If I had 5 textboxes and 5 labels
> >
> > iflag = 3
> > for i = 1 to 5
> > userform1.Controls("Label" & i).Caption = "Item" & iflag*5 + i
> > userform1.controls("Textbox" & i).Value = ""
> > Next
> >
> > If I wanted to show the form as having the labels for virtual textboxes
1
> > to 5 I set iflag to 0
> > for 6 to 10 set iflag to 1
> > for 11 to 15 set iflag to 2
> > for 16 to 20 set iflag to 3
> >
> > Obviously this requires a bit more code and adds some complexity, but
that
> > complexity is trivial to what you are trying to do. With the above
concept,
> > I could represent 10, 1500, 3000 virtual textboxes with minimal
additional
> > work. I understand the labels captions wouldn't be item1 to item1500,
but
> > you can always have an array with the 1500 unique captions and index
into
> > the array.
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Liedson31" <[email protected]> wrote in message
> > news:[email protected]...
> > > thanks again Bob:
> > > if you remember my previous problem(1.500 textboxes
say,input
> > > this code into all the textboxes will be a nightmare !!! but it´s the
only
> > > solution isn´t it?
> > > what i was askinhg, if is there other solution like format the textbox
in
> > > the form open,is there a format property of textbox(like cells format
of
> > > excel)...i don´t think so to
have
> > to
> > > put this code in all the textboxes .....
> > >
> > > the 3) question mean this:
> > > i have a textbox1 and a textbox2...what i want is when i put a value
in
> > > textbox1 the textbox2 must have the same value and either to textbox2
> > > input.....
> > > what i want is something like textbox2=textbox1 but the only way o
know is
> > > the
> > > Private Sub.... Change()
> > > textbox2=textbox1
> > > end sub
> > >
> > > is there a way of making this without write code,i tried in the
> > > controlsource but i can´t do it..in other hand i want to make
something
> > like
> > > this to
> > >
> > > textbox1,textbox2,textbox3,this textboxes are all in a frame..... and
i
> > want
> > > to have a textbox(n) that is the sum of all of them. but in a frame
only
> > can
> > > do this with the change event, so if i have 2 frames when i jump into
> > frame2
> > > textbox(n) takes the sum value of all,but if i jump from textbox1 to
> > textbox2
> > > the textbox(n) does´nt take the sum value............
> > >
> > > can you help with this 2 problems.....and once more thank you very
much
> > Bob
> > >
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > 1)
> > > >
> > > > Private Sub ZipCode_KeyDown(ByVal KeyCode As MSForms.ReturnInteger,
_
> > > > ByVal Shift As Integer )
> > > > With Me.Textbox1
> > > > Select Case True
> > > > Case (KeyCode >= 96 And KeyCode < 105) 'numeric keypad
> > > > 'exit quietly
> > > > Case (KeyCode >= 48 And KeyCode < 57) 'normal keypad
> > > > 'exit quietly
> > > > Case KeyCode = 8 'backspace
> > > > If Len(.Text) > 0 Then
> > > > .Text = Left(.Text, Len(.Text) - 1)
> > > > End If
> > > > KeyCode = 0
> > > > Case KeyCode = 46 ' decimal point
> > > > Case Else
> > > > KeyCode = 0
> > > > Beep
> > > > End Select
> > > > End With
> > > > End Sub
> > > >
> > > > 2)
> > > >
> > > > Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
> > > > Dim iPos As Long
> > > > With Me.TextBox1
> > > > iPos = InStr(1, .Text, ".")
> > > > If iPos > 0 Then
> > > > If iPos <> Len(.Text) - 2 Then
> > > > MsgBox "Invalid amount"
> > > > Cancel = True
> > > > End If
> > > > End If
> > > > End With
> > > > End Sub
> > > >
> > > > 3) What do you mean by '... without the change event code ...'? Why
not
> > just
> > > > set it to the other textbox value?
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "Liedson31" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > hello everyone...
> > > > > i have a few questions about textboxes and the answer is i suspect
> > > > simple...
> > > > > --by code i know the answer but it´s the only option i have for
this?
> > > > > 1) only let the user to insert numbers in a textbox (without code
> > > > isnumeric)
> > > > > 2) the textbox must be formated with 2 decimals (without
> > > > format(....,"0,00"))
> > > > > 3) 2 textboxes must have the same value so if i input in one of
them
> > let´s
> > > > > say the number 5 the other must list 5(without the change event
code)
> > > > >
> > > > >
> > > > > thanks for any help
> > > > > Miguel
> > > >
> > > >
> > > >
> >
> >
> >
hi again...
the solution solve my problem,but now i have another
imagine..i have:
- textbox1(control source = a1)
- textbox2(control source = a2)
in cell a3 i´ve got =sum(a1:a2),what i want was that
- textbox3(control source = a3), but if i did that i overwrite the function
sum
is possible that i have the textbox3 with the sum value wythout type code,
just link it to a cell.
"Bob Phillips" wrote:
> In the controlsource property, just put the cell string, like A1. If you
> want to specify a sheet, then use 'sheet name'!A1
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Liedson31" <[email protected]> wrote in message
> news:[email protected]...
> > thanks Tom, you helped me a lot but there is a last question...sorry for
> > that...
> > how can i make the link between the textbox and the cell...
> > can i use the controlsource?.....i´ve tried to use it but it gives me an
> > error... i already use what you suggested me but i can´t update the
> textbox
> > because i can´t link it to the cell...unless i wrote
> textbox=cells(x,y),but i
> > believe there is a way of doing that without code.
> >
> > thanks again
> >
> >
> > "Tom Ogilvy" wrote:
> >
> > > This methodology can be adapted to textboxes:
> > >
> > > http://j-walk.com/ss/excel/tips/tip44.htm
> > >
> > > None of what you want to do is supported without using events and code.
> > >
> > > You can only link to a cell in a sheet to the best of my knowledge. If
> you
> > > did that and then linked a cell containing a sum formula to the sum
> textbox,
> > > the formula would get overwritten when the value in the cell changed.
> > >
> > > I still can't imagine a situation where you would need 1500 textboxes
> all
> > > visible at the same time - and if that isn't the requirement (that the
> be
> > > visible all at the same time), then there is no requirement for 1500
> actual
> > > textboxes. You really need to be more creative in your thinking.
> > >
> > > for example, If I had 5 textboxes and 5 labels
> > >
> > > iflag = 3
> > > for i = 1 to 5
> > > userform1.Controls("Label" & i).Caption = "Item" & iflag*5 + i
> > > userform1.controls("Textbox" & i).Value = ""
> > > Next
> > >
> > > If I wanted to show the form as having the labels for virtual textboxes
> 1
> > > to 5 I set iflag to 0
> > > for 6 to 10 set iflag to 1
> > > for 11 to 15 set iflag to 2
> > > for 16 to 20 set iflag to 3
> > >
> > > Obviously this requires a bit more code and adds some complexity, but
> that
> > > complexity is trivial to what you are trying to do. With the above
> concept,
> > > I could represent 10, 1500, 3000 virtual textboxes with minimal
> additional
> > > work. I understand the labels captions wouldn't be item1 to item1500,
> but
> > > you can always have an array with the 1500 unique captions and index
> into
> > > the array.
> > >
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Liedson31" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > thanks again Bob:
> > > > if you remember my previous problem(1.500 textboxes
> say,input
> > > > this code into all the textboxes will be a nightmare !!! but it´s the
> only
> > > > solution isn´t it?
> > > > what i was askinhg, if is there other solution like format the textbox
> in
> > > > the form open,is there a format property of textbox(like cells format
> of
> > > > excel)...i don´t think so to
> have
> > > to
> > > > put this code in all the textboxes .....
> > > >
> > > > the 3) question mean this:
> > > > i have a textbox1 and a textbox2...what i want is when i put a value
> in
> > > > textbox1 the textbox2 must have the same value and either to textbox2
> > > > input.....
> > > > what i want is something like textbox2=textbox1 but the only way o
> know is
> > > > the
> > > > Private Sub.... Change()
> > > > textbox2=textbox1
> > > > end sub
> > > >
> > > > is there a way of making this without write code,i tried in the
> > > > controlsource but i can´t do it..in other hand i want to make
> something
> > > like
> > > > this to
> > > >
> > > > textbox1,textbox2,textbox3,this textboxes are all in a frame..... and
> i
> > > want
> > > > to have a textbox(n) that is the sum of all of them. but in a frame
> only
> > > can
> > > > do this with the change event, so if i have 2 frames when i jump into
> > > frame2
> > > > textbox(n) takes the sum value of all,but if i jump from textbox1 to
> > > textbox2
> > > > the textbox(n) does´nt take the sum value............
> > > >
> > > > can you help with this 2 problems.....and once more thank you very
> much
> > > Bob
> > > >
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > 1)
> > > > >
> > > > > Private Sub ZipCode_KeyDown(ByVal KeyCode As MSForms.ReturnInteger,
> _
> > > > > ByVal Shift As Integer )
> > > > > With Me.Textbox1
> > > > > Select Case True
> > > > > Case (KeyCode >= 96 And KeyCode < 105) 'numeric keypad
> > > > > 'exit quietly
> > > > > Case (KeyCode >= 48 And KeyCode < 57) 'normal keypad
> > > > > 'exit quietly
> > > > > Case KeyCode = 8 'backspace
> > > > > If Len(.Text) > 0 Then
> > > > > .Text = Left(.Text, Len(.Text) - 1)
> > > > > End If
> > > > > KeyCode = 0
> > > > > Case KeyCode = 46 ' decimal point
> > > > > Case Else
> > > > > KeyCode = 0
> > > > > Beep
> > > > > End Select
> > > > > End With
> > > > > End Sub
> > > > >
> > > > > 2)
> > > > >
> > > > > Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
> > > > > Dim iPos As Long
> > > > > With Me.TextBox1
> > > > > iPos = InStr(1, .Text, ".")
> > > > > If iPos > 0 Then
> > > > > If iPos <> Len(.Text) - 2 Then
> > > > > MsgBox "Invalid amount"
> > > > > Cancel = True
> > > > > End If
> > > > > End If
> > > > > End With
> > > > > End Sub
> > > > >
> > > > > 3) What do you mean by '... without the change event code ...'? Why
> not
> > > just
> > > > > set it to the other textbox value?
> > > > >
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > > RP
> > > > > (remove nothere from the email address if mailing direct)
> > > > >
> > > > >
> > > > > "Liedson31" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > hello everyone...
> > > > > > i have a few questions about textboxes and the answer is i suspect
> > > > > simple...
> > > > > > --by code i know the answer but it´s the only option i have for
> this?
> > > > > > 1) only let the user to insert numbers in a textbox (without code
> > > > > isnumeric)
> > > > > > 2) the textbox must be formated with 2 decimals (without
> > > > > format(....,"0,00"))
> > > > > > 3) 2 textboxes must have the same value so if i input in one of
> them
> > > let´s
> > > > > > say the number 5 the other must list 5(without the change event
> code)
> > > > > >
> > > > > >
> > > > > > thanks for any help
> > > > > > Miguel
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>
Wait I have an idea, use a spreadsheet. They have thousands of text boxes,
even some numeric ones. I’ve seen this approach before. Go outside take a
deep breath and rethink the objective. If you feel you still need that many
textboxes walk around abit until a solution becomes apparent.
--
Jim at Eagle
"Liedson31" wrote:
> hi again...
> the solution solve my problem,but now i have another
> imagine..i have:
> - textbox1(control source = a1)
> - textbox2(control source = a2)
> in cell a3 i´ve got =sum(a1:a2),what i want was that
> - textbox3(control source = a3), but if i did that i overwrite the function
> sum
>
> is possible that i have the textbox3 with the sum value wythout type code,
> just link it to a cell.
>
>
>
> "Bob Phillips" wrote:
>
> > In the controlsource property, just put the cell string, like A1. If you
> > want to specify a sheet, then use 'sheet name'!A1
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Liedson31" <[email protected]> wrote in message
> > news:[email protected]...
> > > thanks Tom, you helped me a lot but there is a last question...sorry for
> > > that...
> > > how can i make the link between the textbox and the cell...
> > > can i use the controlsource?.....i´ve tried to use it but it gives me an
> > > error... i already use what you suggested me but i can´t update the
> > textbox
> > > because i can´t link it to the cell...unless i wrote
> > textbox=cells(x,y),but i
> > > believe there is a way of doing that without code.
> > >
> > > thanks again
> > >
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > This methodology can be adapted to textboxes:
> > > >
> > > > http://j-walk.com/ss/excel/tips/tip44.htm
> > > >
> > > > None of what you want to do is supported without using events and code.
> > > >
> > > > You can only link to a cell in a sheet to the best of my knowledge. If
> > you
> > > > did that and then linked a cell containing a sum formula to the sum
> > textbox,
> > > > the formula would get overwritten when the value in the cell changed.
> > > >
> > > > I still can't imagine a situation where you would need 1500 textboxes
> > all
> > > > visible at the same time - and if that isn't the requirement (that the
> > be
> > > > visible all at the same time), then there is no requirement for 1500
> > actual
> > > > textboxes. You really need to be more creative in your thinking.
> > > >
> > > > for example, If I had 5 textboxes and 5 labels
> > > >
> > > > iflag = 3
> > > > for i = 1 to 5
> > > > userform1.Controls("Label" & i).Caption = "Item" & iflag*5 + i
> > > > userform1.controls("Textbox" & i).Value = ""
> > > > Next
> > > >
> > > > If I wanted to show the form as having the labels for virtual textboxes
> > 1
> > > > to 5 I set iflag to 0
> > > > for 6 to 10 set iflag to 1
> > > > for 11 to 15 set iflag to 2
> > > > for 16 to 20 set iflag to 3
> > > >
> > > > Obviously this requires a bit more code and adds some complexity, but
> > that
> > > > complexity is trivial to what you are trying to do. With the above
> > concept,
> > > > I could represent 10, 1500, 3000 virtual textboxes with minimal
> > additional
> > > > work. I understand the labels captions wouldn't be item1 to item1500,
> > but
> > > > you can always have an array with the 1500 unique captions and index
> > into
> > > > the array.
> > > >
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > > "Liedson31" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > thanks again Bob:
> > > > > if you remember my previous problem(1.500 textboxes
> > say,input
> > > > > this code into all the textboxes will be a nightmare !!! but it´s the
> > only
> > > > > solution isn´t it?
> > > > > what i was askinhg, if is there other solution like format the textbox
> > in
> > > > > the form open,is there a format property of textbox(like cells format
> > of
> > > > > excel)...i don´t think so to
> > have
> > > > to
> > > > > put this code in all the textboxes .....
> > > > >
> > > > > the 3) question mean this:
> > > > > i have a textbox1 and a textbox2...what i want is when i put a value
> > in
> > > > > textbox1 the textbox2 must have the same value and either to textbox2
> > > > > input.....
> > > > > what i want is something like textbox2=textbox1 but the only way o
> > know is
> > > > > the
> > > > > Private Sub.... Change()
> > > > > textbox2=textbox1
> > > > > end sub
> > > > >
> > > > > is there a way of making this without write code,i tried in the
> > > > > controlsource but i can´t do it..in other hand i want to make
> > something
> > > > like
> > > > > this to
> > > > >
> > > > > textbox1,textbox2,textbox3,this textboxes are all in a frame..... and
> > i
> > > > want
> > > > > to have a textbox(n) that is the sum of all of them. but in a frame
> > only
> > > > can
> > > > > do this with the change event, so if i have 2 frames when i jump into
> > > > frame2
> > > > > textbox(n) takes the sum value of all,but if i jump from textbox1 to
> > > > textbox2
> > > > > the textbox(n) does´nt take the sum value............
> > > > >
> > > > > can you help with this 2 problems.....and once more thank you very
> > much
> > > > Bob
> > > > >
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > 1)
> > > > > >
> > > > > > Private Sub ZipCode_KeyDown(ByVal KeyCode As MSForms.ReturnInteger,
> > _
> > > > > > ByVal Shift As Integer )
> > > > > > With Me.Textbox1
> > > > > > Select Case True
> > > > > > Case (KeyCode >= 96 And KeyCode < 105) 'numeric keypad
> > > > > > 'exit quietly
> > > > > > Case (KeyCode >= 48 And KeyCode < 57) 'normal keypad
> > > > > > 'exit quietly
> > > > > > Case KeyCode = 8 'backspace
> > > > > > If Len(.Text) > 0 Then
> > > > > > .Text = Left(.Text, Len(.Text) - 1)
> > > > > > End If
> > > > > > KeyCode = 0
> > > > > > Case KeyCode = 46 ' decimal point
> > > > > > Case Else
> > > > > > KeyCode = 0
> > > > > > Beep
> > > > > > End Select
> > > > > > End With
> > > > > > End Sub
> > > > > >
> > > > > > 2)
> > > > > >
> > > > > > Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
> > > > > > Dim iPos As Long
> > > > > > With Me.TextBox1
> > > > > > iPos = InStr(1, .Text, ".")
> > > > > > If iPos > 0 Then
> > > > > > If iPos <> Len(.Text) - 2 Then
> > > > > > MsgBox "Invalid amount"
> > > > > > Cancel = True
> > > > > > End If
> > > > > > End If
> > > > > > End With
> > > > > > End Sub
> > > > > >
> > > > > > 3) What do you mean by '... without the change event code ...'? Why
> > not
> > > > just
> > > > > > set it to the other textbox value?
> > > > > >
> > > > > > --
> > > > > >
> > > > > > HTH
> > > > > >
> > > > > > RP
> > > > > > (remove nothere from the email address if mailing direct)
> > > > > >
> > > > > >
> > > > > > "Liedson31" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > hello everyone...
> > > > > > > i have a few questions about textboxes and the answer is i suspect
> > > > > > simple...
> > > > > > > --by code i know the answer but it´s the only option i have for
> > this?
> > > > > > > 1) only let the user to insert numbers in a textbox (without code
> > > > > > isnumeric)
> > > > > > > 2) the textbox must be formated with 2 decimals (without
> > > > > > format(....,"0,00"))
> > > > > > > 3) 2 textboxes must have the same value so if i input in one of
> > them
> > > > let´s
> > > > > > > say the number 5 the other must list 5(without the change event
> > code)
> > > > > > >
> > > > > > >
> > > > > > > thanks for any help
> > > > > > > Miguel
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks