VBA Rowsource with variable

Asked

Viewed 725 times

0

How do I place a variable spreadsheet in a Rowsource? I’m trying to concatenate with the last line but this giving error:

Private Sub UserForm_Initialize()
Dim plan As Worksheet
Dim lin As Range
Dim fim As Range

Optionapos = True

If Optionapos = True Then
    Set plan = Worksheets("CadReu")
Else
    Set plan = Worksheets("CadReuOlde")
End If

With plan

'atualiza a caixa localizar
Dim totaldelinhas As Integer
totaldelinhas = plan.UsedRange.Rows.Count

'formreunioes.cxLocalizar2.ColumnCount = .Columns.Count
formreunioes.cxLocalizar2.RowSource = .Range("A1:A" & totaldelinhas)

End With
vba

  • You didn’t say what the error code was. What may have happened is that you were passing the value that came from plan.Range("A1:A" & totaldelinhas) and not the text value that defined Rowsource. Anyway you decided, I suggest moving the answer to where effectively it should be the answer and then mark as correct. It is not readable to edit the question by putting the answer. Hug.

  • OK thank you so much for your reply and for your tip, I already arranged the site of the reply.

1 answer

1

I decided to change the code especially the type of the variable plan As Worksheet to string and worked, as follows:

Private Sub UserForm_Initialize()
Dim plan As String
Dim lin As Range
Dim fim As Range

Optionapos = True

If Optionapos = True Then
plan = "CadReu"
Else
plan = "CadReuOlde"
End If

'atualiza a caixa localizar
Dim totaldelinhas As Long
totaldelinhas = Worksheets(plan).UsedRange.Rows.Count

formreunioes.cxLocalizar2.RowSource = plan & "!" & "A2:A" & totaldelinhas

Browser other questions tagged vba

You are not signed in. Login or sign up in order to post.