Beginner asking for advice

[Tracey]

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

 

[Frank Kabel]

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

 

[Tracey]

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

 

[Bob Phillips]

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)

 

[Bob Kilmer]

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]

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

 

[Bob Phillips]

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]

the variable is declared as an integer

 

[Steve Garman]

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

 

[Bob Phillips]

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)