Control Access

[Jay]

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.

 

[OssieMac]

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

 

[OssieMac]

Change Dim statement
Dim objCtrl As OLEObject
in lieu of Dim objCtrl As Object

 

[Jay]

Thank you, OssieMac

Jae

Change Dim statement
Dim objCtrl As OLEObject
in lieu of Dim objCtrl As Object