Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel spin box - no Control tab in Format Control dialong box | Excel Worksheet Functions | |||
Difference between a Forms Control verus Active-X Control | Excel Discussion (Misc queries) | |||
Configuring Excel 2003 to control access to Trusted Publishers | Excel Discussion (Misc queries) | |||
In EXCEL,How do you control the access to SHEET2, based on a value | Excel Worksheet Functions | |||
Control Tab from Combo box- format control missing!! | Excel Discussion (Misc queries) |