Beginner asking for advice
 

Help, what's wrong with this?

Sub manual_ta()


Dim newnum As Variant 'newnum for user-entered ta
Dim digits As Single 'digits used for capturing length of user entry


newnum = inputbox("What number would you like to assign to this TA
Click OK after entering number.", [TA number], vbOKCancel)


digits = Len(newnum)
If digits = 6 Then
Range("TA").ClearContents 'clear the old TA #
newnum = ActiveCell.Value 'paste the value of the user input into tha
cell if it is 6 digits in length

Else
Call question

End If


End Su

--
Message posted from http://www.ExcelForum.com

Beginner asking for advice
 

Hi
you may explain what you're trying to achieve with this code

--
Regards
Frank Kabel
Frankfurt, Germany


Help, what's wrong with this?

Sub manual_ta()


Dim newnum As Variant 'newnum for user-entered ta
Dim digits As Single 'digits used for capturing length of user entry


newnum = inputbox("What number would you like to assign to this TA?
Click OK after entering number.", [TA number], vbOKCancel)


digits = Len(newnum)
If digits = 6 Then
Range("TA").ClearContents 'clear the old TA #
newnum = ActiveCell.Value 'paste the value of the user input into
that
cell if it is 6 digits in length

Else
Call question

End If


End Sub


---
Message posted from http://www.ExcelForum.com/

Beginner asking for advice
 

Ok, here's what I'm trying to do: I got the code to work but the Len(
function doesn't appear to work. I'm capturing user input via a
InputBox, and testing it to see if it's 6 digits in length. If i
isn't, another function will be called. However if it is 6 digits i
length, the function should insert the user input 6-digit value int
the range "TA". Here again is the code. Any input about why the len(
feature isn't working here would be great (I tested it by entering
digit #'s and it calls the other function as if it were <6. se
variable titled digits for this value.

Function manual_ta()

Dim newnumrng As Range 'user input
Dim digits As Integer 'digits used for capturing length of user entry

On Error Resume Next
Set newnumrng = Application.inputbox(prompt:="What number would yo
like to assign to this TA? Click OK after entering number.", Type:=1)

digits = Len(newnumrng)
If digits < 6 Then
Call question
Else

Range("TA").ClearContents 'clear the old TA #
newnumrng = ActiveCell.Value 'paste the value of the user input int
that cell if it is 6 digits in length


End I

--
Message posted from http://www.ExcelForum.com

Beginner asking for advice
 

Probably because you are using set newnumrng, which is suggesting an object
not a string.

Try

newnumrng = inputbox(prompt:="What number would you like to assign to this
TA? Click OK after entering number."

--

HTH

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

"Tracey " wrote in message
...
Ok, here's what I'm trying to do: I got the code to work but the Len()
function doesn't appear to work. I'm capturing user input via an
InputBox, and testing it to see if it's 6 digits in length. If it
isn't, another function will be called. However if it is 6 digits in
length, the function should insert the user input 6-digit value into
the range "TA". Here again is the code. Any input about why the len()
feature isn't working here would be great (I tested it by entering 6
digit #'s and it calls the other function as if it were <6. see
variable titled digits for this value.

Function manual_ta()

Dim newnumrng As Range 'user input
Dim digits As Integer 'digits used for capturing length of user entry

On Error Resume Next
Set newnumrng = Application.inputbox(prompt:="What number would you
like to assign to this TA? Click OK after entering number.", Type:=1)

digits = Len(newnumrng)
If digits < 6 Then
Call question
Else

Range("TA").ClearContents 'clear the old TA #
newnumrng = ActiveCell.Value 'paste the value of the user input into
that cell if it is 6 digits in length


End If


---
Message posted from http://www.ExcelForum.com/

Beginner asking for advice
 

Replace "Set" with "Let" (or nothing at all). Set is used to assign object
references only. InputBox only returns an object when Type = 8, but using by
Set, you are forcing to be an object.

"Tracey " wrote in message
...
Ok, here's what I'm trying to do: I got the code to work but the Len()
function doesn't appear to work. I'm capturing user input via an
InputBox, and testing it to see if it's 6 digits in length. If it
isn't, another function will be called. However if it is 6 digits in
length, the function should insert the user input 6-digit value into
the range "TA". Here again is the code. Any input about why the len()
feature isn't working here would be great (I tested it by entering 6
digit #'s and it calls the other function as if it were <6. see
variable titled digits for this value.

Function manual_ta()

Dim newnumrng As Range 'user input
Dim digits As Integer 'digits used for capturing length of user entry

On Error Resume Next
Set newnumrng = Application.inputbox(prompt:="What number would you
like to assign to this TA? Click OK after entering number.", Type:=1)

digits = Len(newnumrng)
If digits < 6 Then
Call question
Else

Range("TA").ClearContents 'clear the old TA #
newnumrng = ActiveCell.Value 'paste the value of the user input into
that cell if it is 6 digits in length


End If


---
Message posted from http://www.ExcelForum.com/

Beginner asking for advice
 

Thanks Bob. Unfortunately that didn't work. The code seems to run jus
fine there are no runtime errors it's just the len() thing that is no
acknowledging that there were actually 6 digits entered. Did
represent the len() feature incorrectly? I declared the variabl
digits as an integer, and then my code states

digits = len(newnumrng) 'newnumrng was user input from inputbox
if digits < 6 then
call another_function

'when user enters 6 digits it calls the other function anyway.
Any help would be so gratefully appreciated.

thanks
trace

--
Message posted from http://www.ExcelForum.com

Beginner asking for advice
 

It might run, but the length of an object is, who knows, but probably not 6.
Is the variable declared as a string, because Len works against strings.

--

HTH

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

"Tracey " wrote in message
...
Thanks Bob. Unfortunately that didn't work. The code seems to run just
fine there are no runtime errors it's just the len() thing that is not
acknowledging that there were actually 6 digits entered. Did I
represent the len() feature incorrectly? I declared the variable
digits as an integer, and then my code states

digits = len(newnumrng) 'newnumrng was user input from inputbox
if digits < 6 then
call another_function

'when user enters 6 digits it calls the other function anyway.
Any help would be so gratefully appreciated.

thanks
tracey


---
Message posted from http://www.ExcelForum.com/

Beginner asking for advice
 

the variable is declared as an integer

--
Message posted from http://www.ExcelForum.com

Beginner asking for advice
 

The big thing that's wrong with it is that

newnum = ActiveCell.Value 'paste the value of the user input into

is the wrong way round and would be better as

ActiveCell.Value = newnum

However, assuming that TA is a named range, I suspect you're looking for
something like this:

Sub manual_ta()

Dim newnum As Variant 'newnum for user-entered ta
Dim digits As Single 'digits used for capturing length of user entry

newnum = InputBox("What value would you like in TA?")

digits = Len(newnum)
If digits = 6 Then
Range("TA").Value = newnum
Else
Call question
End If
End Sub


Tracey < wrote:
Help, what's wrong with this?

Sub manual_ta()


Dim newnum As Variant 'newnum for user-entered ta
Dim digits As Single 'digits used for capturing length of user entry


newnum = inputbox("What number would you like to assign to this TA?
Click OK after entering number.", [TA number], vbOKCancel)


digits = Len(newnum)
If digits = 6 Then
Range("TA").ClearContents 'clear the old TA #
newnum = ActiveCell.Value 'paste the value of the user input into that
cell if it is 6 digits in length

Else
Call question

End If


End Sub


---
Message posted from http://www.ExcelForum.com/

Beginner asking for advice
 

So declare it as string.

--

HTH

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

"Tracey " wrote in message
...
the variable is declared as an integer.


---
Message posted from http://www.ExcelForum.com/