Selecting a numeric value from a list box using a macro

[#1]

Hello

I am having problems with selecting a numeric value from a list box using a macro. I have set up a very simple qv model to illustrate the problem. In the attached example you will see that I have :

- A table box showing some simple data (First name, Last name and Age) which I typed into excel and imported into Qlikview

- 2 very simple macros which are called from the 2 buttons

The first macro which selects the first name 'Steven' from the FName list box works fine whereas the 2nd macro which selectes the age 29 from the Age Listbox  does not. If I change the line

fv(0).Text = "29" to be fv(0).Text = "Unknown" - this works ok

I assume this is because a text string works ok but a numeric value does not.

Any ideas how I can get the select on a number (ie age 29) to work ?

My macros are listed below and I have attached the qlikview model.

Thanks in advance !

Shirley

Sub SelectFName()

    set f = ActiveDocument.Fields("FName")

    set fv = f.GetNoValues  'empty array

    fv.Add

    fv(0).Text = "Steven"

    f.SelectValues fv

'

end sub

Sub SelectAge()

    set f = ActiveDocument.Fields("Age")

    set fv = f.GetNoValues  'empty array

    fv.Add

    fv(0).Text = "29"

    f.SelectValues fv

'

end sub

[m_woolf]

Why not just:

ActiveDocument.Fields("Age").Select 29

[marcus_sommer]

I'm not sure if you really needs macros for this. Mostly actions could do the same. See also: Macros are Bad.

- Marcus

[#4]

Hello

Thanks for your answer. It is helpful but in my macro I actually want to select a number of values from the list box - (I just posted on here a very simple qlikview model showing 1 item I want to select).

The way I want it to work is that the user enters a comma separated list of values into an input box and they then click on a button that runs a macro which selects those items in the list box (see screenshot below). I have 2 listboxes and 2 input boxes (1 for age and 1 for FirstName). The Age input box uses the var vAgeList and the FirstName input box uses the var vFNameList. The FirstName button works fine and selects the required items but the age one does not (except if I put 'Unknown' into the input box). So I can only get my method to work with text data - how can I get it to work with numbers ? Please See scripts below :

Thanks Shirley

function getVariable(varName)

     set v = ActiveDocument.Variables(varName)

     getVariable = v.GetContent.String

end function

Sub SelectFName()

    set oFNList = ActiveDocument.Fields("FName")

    set FNListValues = oFNList.GetNoValues 'empty array

    FNList=Split(getVariable("vFNameList"), ",")

    i=0

    For Each FN in FNList

        FNListValues.Add

        FNListValues(i).Text = trim(FN)

        i=i+1

    Next

    oFNList.SelectValues FNListValues

End Sub

Sub SelectAge()

    set oAgeList = ActiveDocument.Fields("Age")

    set AgeListValues = oAgeList.GetNoValues 'empty array

    AgeList=Split(getVariable("vAgeList"), ",")

    i=0

    For Each Age in AgeList

        AgeListValues.Add

        AgeListValues(i).Text = trim(Age)

        i=i+1

    Next

    oAgeList.SelectValues AgeListValues

End Sub

[#5]

Hello Marcus

Thanks for your answer. I know that macros should only be used where necessary - however I put a 'cut-down' version of what I wanted to do as a post to try and simplify it - I should have described the full picture. I am trying to select a number of values from the list box from a comma separated list in an input box. It works fine work with text data but not with numbers ('age' in my example). Any ideas ?

Pls see the full description I've put in the reply to M Woolf. I've actually updated my qlikview model to demonstrate the problem but I'm not sure how to post a qlikview model in a 'reply' here.

thanks for your help

Shirley

[marcus_sommer · Accepted solution]

This is the pdf of a german posting to the same topic: Inputbox not more then 25000 Char's and I think you will find here more hints: https://community.qlik.com/search.jspa?q=inputbox+macro

If your input-listings isn't very big (< 1024 chars) it could be easier to try it with normal selecting-list:

(value1|value2|value3)

or with a selction-action.

- Marcus

[#7]

Thanks Marcus

Much appreciated

I followed some of the links and eventually found this article which does exactly what I want without a macro !

Search by copy and pasting from an external list - The Qlik Fix! The Qlik Fix!

Shirley