Control Access
 

Thanks in advance

Sheet1 has several controls from control toolbox.
Sheet2 has a command button (name: Update record) with 'Click' event.

Question, please
In the 'Click' event in sheet2, I want to access the values of contols in
sheet1.

Control Access
 

Hi Jay,

Not sure if you just want the code for one individual control at a time or
if you want to cycle through all controls so here's both.

Private Sub CommandButton1_Click()
'Individual control
Dim myVariable As Variant

myVariable = Sheets("Sheet1").ComboBox1.Value

MsgBox myVariable


End Sub

Private Sub CommandButton2_Click()

Dim objCtrl As Object
Dim strCtrlname As String
Dim ctrlValue As Variant 'Variant for all data types

With Sheets("Sheet1")
For Each objCtrl In .OLEObjects
strCtrlname = objCtrl.Name
ctrlValue = objCtrl.Object.Value
MsgBox strCtrlname & " " & ctrlValue
Next objCtrl
End With

End Sub



--
Regards,

OssieMac


"Jay" wrote:

Thanks in advance

Sheet1 has several controls from control toolbox.
Sheet2 has a command button (name: Update record) with 'Click' event.

Question, please
In the 'Click' event in sheet2, I want to access the values of contols in
sheet1.

Control Access
 

Change Dim statement
Dim objCtrl As OLEObject
in lieu of Dim objCtrl As Object
--
Regards,

OssieMac


"OssieMac" wrote:

Hi Jay,

Not sure if you just want the code for one individual control at a time or
if you want to cycle through all controls so here's both.

Private Sub CommandButton1_Click()
'Individual control
Dim myVariable As Variant

myVariable = Sheets("Sheet1").ComboBox1.Value

MsgBox myVariable


End Sub

Private Sub CommandButton2_Click()

Dim objCtrl As Object
Dim strCtrlname As String
Dim ctrlValue As Variant 'Variant for all data types

With Sheets("Sheet1")
For Each objCtrl In .OLEObjects
strCtrlname = objCtrl.Name
ctrlValue = objCtrl.Object.Value
MsgBox strCtrlname & " " & ctrlValue
Next objCtrl
End With

End Sub



--
Regards,

OssieMac


"Jay" wrote:

Thanks in advance

Sheet1 has several controls from control toolbox.
Sheet2 has a command button (name: Update record) with 'Click' event.

Question, please
In the 'Click' event in sheet2, I want to access the values of contols in
sheet1.

Control Access
 

Thank you, OssieMac

Jae

"OssieMac" wrote:

Change Dim statement
Dim objCtrl As OLEObject
in lieu of Dim objCtrl As Object
--
Regards,

OssieMac


"OssieMac" wrote:

Hi Jay,

Not sure if you just want the code for one individual control at a time or
if you want to cycle through all controls so here's both.

Private Sub CommandButton1_Click()
'Individual control
Dim myVariable As Variant

myVariable = Sheets("Sheet1").ComboBox1.Value

MsgBox myVariable


End Sub

Private Sub CommandButton2_Click()

Dim objCtrl As Object
Dim strCtrlname As String
Dim ctrlValue As Variant 'Variant for all data types

With Sheets("Sheet1")
For Each objCtrl In .OLEObjects
strCtrlname = objCtrl.Name
ctrlValue = objCtrl.Object.Value
MsgBox strCtrlname & " " & ctrlValue
Next objCtrl
End With

End Sub



--
Regards,

OssieMac


"Jay" wrote:

Thanks in advance

Sheet1 has several controls from control toolbox.
Sheet2 has a command button (name: Update record) with 'Click' event.

Question, please
In the 'Click' event in sheet2, I want to access the values of contols in
sheet1.