Trying to point to different listboxes 1

[kalle82]

Why is this not working? Im working in Excel 2003.

I thought I could set it to a different listbox depending on the value of my page variable?

Do I need to somehow set my dynamic variable as a listbox control?

Dim logfilepath As String
Dim dynamicbox As String

Select Case page

Case Is = 1

dynamicbox = ListBox1

Case Is = 2

dynamicbox = ListBox2

Case Is = 3

dynamicbox = ListBox3

Case Is = 4

dynamicbox = ListBox4

Case Is = 5

dynamicbox = ListBox5

End Select

logfilepath = "k:\handläggningsstöd\Ärenden\logg-för-" & logfilename & ".ini"

If FileOrDirExists(logfilepath) Then

MsgBox ("Det finns en LOGGFIL!")

UserForm1.dynamicbox.Clear

Dim lFile As Long
Dim varjerad As String

lFile = FreeFile()
     
    Open logfilepath For Input As lFile
     
    While Not EOF(lFile)
         
         
        Line Input #lFile, varjerad
         
        UserForm1.dynamicbox.AddItem varjerad
         
         
    Wend
     
     
    Close lFile

Else

MsgBox ("DET FINNS ABSOLUT INGEN LOGG FIL")

End If

 

[SkipVought]

hi,

Are you trying to assign an Object to a string? That will not work!

Also, exactly where is this code?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

kalle82,

I think you are trying to address the listboxes using the line:

UserForm1.dynamicbox.AddItem varjerad

Try this instead:

UserForm1.controls(dynamicbox).AddItem varjerad

You will also need to put quotes around the names. In other words, change the line:

dynamicbox = ListBox1

to:

dynamicbox = "ListBox1"

etc...

Tony

 

[kalle82]

Thanks that worked out perfectly

 

[SkipVought]

if that's the case then the entire select case statement can be replaced by one statement...

dynamicbox = "ListBox" & page

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

Skip,

True. He could also address the LBs directly by dimming dynamicbox as a control and handling the issue that way, but I thought I'd keep it simple and just address the main issue.

kalle82,
If you follow Skip's advice, you need to make sure that page is dimmed as a string, or use:

dynamicbox = "ListBox" & trim(str(page))

Tony

 

[SkipVought]

If the variable, page, is an integer data type (which the OP's Select Case statement implies), then no trimming is required, and the vb complier does the string convertion

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kalle82]

Hey guys! Wow thanks I love shorten my code, and doing it your way will most certainly be more effective!

Thanks again, for going the extra mile on this post!

 

[N1GHTEYES]

Skip,

I've just done a quick test, and it looks like you are right about not needing the Trim(). But this is a bit odd, becasue here is a quote from the help on the Str function:

Str Function Example
This example uses the Str function to return a string representation of a number. When a number is converted to a string, a leading space is always reserved for its sign.

Dim MyString
MyString = Str(459) ' Returns " 459".

I used the Trim() to avoid the leading space I was expecting to be there.

As far as the explicit conversion is concerned, you are right, the compiler would do it, but I just prefer to be explicit - it feels safer somehow.

Tony

 

[SkipVought]

Try using the CStr function, rather than the Str function.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}