how to reference worksheet as control source with space in name
 

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

how to reference worksheet as control source with space in name
 

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

"Peter Bailey" wrote in message
...
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

how to reference worksheet as control source with space in name
 

at last such a small thing!

regards
Peter
"Bob Phillips" wrote in message
...
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

"Peter Bailey" wrote in message
...
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

how to reference worksheet as control source with space in name
 

I like this so I don't have to worry:

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



Peter Bailey wrote:

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

--

Dave Peterson