how to reference worksheet as control source with space in name

  • Thread starter Thread starter Peter Bailey
  • Start date Start date
P

Peter Bailey

My worksheet was called "Audit Checklist" and in VBA I reffered to it as :
LearnerCount = Worksheets("Audit Checklist").Range("d2").Value

and all works fine.

I have created a userform now and wanted to amend some cells from selected
data so:

Private Sub UserForm_Initialize()
Me.CBCentre.ControlSource = "Audit Checklist!c4"
Me.CBStaffName.ControlSource = "Audit Checklist!P4"
End Sub

this caused ar error for the control source becuase it wanted:
"AuditChecklist!P4"

how do I reference without renaming the worksheet?

regards
peter
 
Peter,

It's the embedded spaces,

try
Private Sub UserForm_Initialize()
Me.CBCentre.ControlSource = "'Audit Checklist!'c4"
Me.CBStaffName.ControlSource = "'Audit Checklist'!P4"
End Sub
 
at last such a small thing!

regards
Peter
Bob Phillips said:
Peter,

It's the embedded spaces,

try
Private Sub UserForm_Initialize()
Me.CBCentre.ControlSource = "'Audit Checklist!'c4"
Me.CBStaffName.ControlSource = "'Audit Checklist'!P4"
End Sub


--

HTH

RP
Click to expand...
 
I like this so I don't have to worry:

Me.CBCentre.ControlSource _
= Worksheets("Audit Checklist").Range("c4").address(external:=true)
 
Back
Top