Selection box to choose from list & insert string in a column

[Marmot3292]

Hi,

I am writing a code to create a file from an existing spreadsheet to be in the correct import format for another system.

The file can be created for different client accounts, call them Client A, Client B etc. although names are more complex than this, hence I want to use some form of validation. I want the code to give the user a prompt to choose the appropriate account from a pre-defined list, and then input their selection in a column for the file where there are data entries in the first column.

So far I have the below:

'7) Insert the Client ID from options

'Dim AccountList As Variant
'Dim AccountSelected As String

'AccountList = Array("Client ACFR", "Client BSRT", "Client PPTE")
'AccountSelected = SelectionBoxSingle(List:=AccountList)

'Range("G2").Select
'Do
'ActiveCell = AccountSelected
'ActiveCell.Offset(1, 0).Select
'Loop Until IsEmpty(ActiveCell.Offset(0, -6))


Does not work

Any help would be gratefully received.

 

[GlennUK]

Can you explain this part in more detail ... I don't understand what you mean:

input their selection in a column for the file where there are data entries in the first column

 

[Marmot3292]

Yes sure. So basically the number of rows that populate in the file is dynamic, it could be anything from 5 to 60 rows. A list of unique IDs gets copied into column A from elsewhere and lines need populating for each row that has an ID.

Basically the data item (text string) that the user would choose from a selection box, would populate in column G, in every row, all the way down to the last row that has data in column A. The same selection would be input into each row.

The way I have done this in the rest of the file with other fields is to use a loop, based on whether column A is or is not blank... (I possibly could have been more efficient here and done some dynamic ranges instead of looping, but had an existing code that I just copied from somewhere else)

Here is a picture of the output format to help:

 

[GlennUK]

Oh, OK, you mean like:

   For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
        If c.Value <> "" Then c.Offset(0, 6).Value = "whatever"
    Next

 

[Marmot3292]

Thanks, however I want there to be a pop up box to choose the 'whatever' as it could be different text strings that I want to user to pick from a pre-defined list...

 

[GlennUK]

What does this part of your code do:

'AccountList = Array("Client ACFR", "Client BSRT", "Client PPTE")
'AccountSelected = SelectionBoxSingle(List:=AccountList)

? (when it's not commented out)

 

[Marmot3292]

What does this part of your code do:

'AccountList = Array("Client ACFR", "Client BSRT", "Client PPTE")
'AccountSelected = SelectionBoxSingle(List:=AccountList)

? (when it's not commented out)

This was my attempt to use a pop up selection box with a pre-defined list of strings to insert in that column. So for the user to pick the 'whatever' from in your example code above. I found something similar online but sadly it doesnt work.

 

[GlennUK]

In what way doesn't it work? We can all try to help you with that. Otherwise you'll have to create a Userform yourself ... have you ever done that before?

 

[Marmot3292]

In what way doesn't it work? We can all try to help you with that. Otherwise you'll have to create a Userform yourself ... have you ever done that before?

Nope I've never used user forms before...

This current code returns:

Compile error: Sub or Function not defined on the bit emboldened below:
AccountSelected = SelectionBoxSingle(List:=AccountList)

 

[GlennUK]

I'd guess you downloaded some code to show a selection box. Describe what you downloaded, and how you've tried to use it.