Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a single cmdButton to perform different tasks
[Excel 2003]
I have a user form with text boxes. I populate the text boxes using the following type of code: Private Sub CommandSheet1Get_Click() Dim ws As Worksheet Set ws = Worksheets("Sheet1") Me.txtname.Value = ws.Range("A1").Value End Sub I can edit the text boxes and then update the spreadsheet with the following code in another command button Private Sub cmdSendtoSheet1_Click() Dim ws As Worksheet Set ws = Worksheets("Sheet1") ws.Range("A1").Value = Me.txtname.Value End Sub Using this approach I can use a number of command buttons to pull data to the text boxes from different sheets 1,2,3....10. However, here is my question/problem. Although I want to have separate buttons for pulling the data to the textboxes, I want to use just a single button for sending the edited data back to the sheets. So what I would need is for the code to keep track of which sheet the data came from and when the button is pressed, would access the suitable subroutine to return the data to the right sheet To put things in perspective I have approx 300 cells on each sheet to deal with. The same cells on each sheet are used for storing the data I work with. Can anyone help? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a single cmdButton to perform different tasks
Create a variable that's outside any of the procedures in your userform module:
Option Explicit Option compare text 'maybe Dim WhichSheet as string Private Sub button1_click() whichsheet = "sheetname associated with button #1" 'do the work End sub Private Sub button2_click() whichsheet = "sheetname associated with button #2" 'do the work End sub ..... Then in the button that returns the values... Private Sub WriteTheValsBtn_Click() if whichsheet = "" then 'this shouldn't happen except when you're testing <vbg msgbox "something bad happened!" exit sub end if with activeworkbook.worksheets(whichsheet) .range("a1").value = me.textbox1.value ... end with end sub Untested, uncompiled. Watch for typos! Roger on Excel wrote: [Excel 2003] I have a user form with text boxes. I populate the text boxes using the following type of code: Private Sub CommandSheet1Get_Click() Dim ws As Worksheet Set ws = Worksheets("Sheet1") Me.txtname.Value = ws.Range("A1").Value End Sub I can edit the text boxes and then update the spreadsheet with the following code in another command button Private Sub cmdSendtoSheet1_Click() Dim ws As Worksheet Set ws = Worksheets("Sheet1") ws.Range("A1").Value = Me.txtname.Value End Sub Using this approach I can use a number of command buttons to pull data to the text boxes from different sheets 1,2,3....10. However, here is my question/problem. Although I want to have separate buttons for pulling the data to the textboxes, I want to use just a single button for sending the edited data back to the sheets. So what I would need is for the code to keep track of which sheet the data came from and when the button is pressed, would access the suitable subroutine to return the data to the right sheet To put things in perspective I have approx 300 cells on each sheet to deal with. The same cells on each sheet are used for storing the data I work with. Can anyone help? -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a single cmdButton to perform different tasks
Dear Dave,
I tried this, but it doesnt seem to work. What do you think? Roger "Dave Peterson" wrote: Create a variable that's outside any of the procedures in your userform module: Option Explicit Option compare text 'maybe Dim WhichSheet as string Private Sub button1_click() whichsheet = "sheetname associated with button #1" 'do the work End sub Private Sub button2_click() whichsheet = "sheetname associated with button #2" 'do the work End sub ..... Then in the button that returns the values... Private Sub WriteTheValsBtn_Click() if whichsheet = "" then 'this shouldn't happen except when you're testing <vbg msgbox "something bad happened!" exit sub end if with activeworkbook.worksheets(whichsheet) .range("a1").value = me.textbox1.value ... end with end sub Untested, uncompiled. Watch for typos! Roger on Excel wrote: [Excel 2003] I have a user form with text boxes. I populate the text boxes using the following type of code: Private Sub CommandSheet1Get_Click() Dim ws As Worksheet Set ws = Worksheets("Sheet1") Me.txtname.Value = ws.Range("A1").Value End Sub I can edit the text boxes and then update the spreadsheet with the following code in another command button Private Sub cmdSendtoSheet1_Click() Dim ws As Worksheet Set ws = Worksheets("Sheet1") ws.Range("A1").Value = Me.txtname.Value End Sub Using this approach I can use a number of command buttons to pull data to the text boxes from different sheets 1,2,3....10. However, here is my question/problem. Although I want to have separate buttons for pulling the data to the textboxes, I want to use just a single button for sending the edited data back to the sheets. So what I would need is for the code to keep track of which sheet the data came from and when the button is pressed, would access the suitable subroutine to return the data to the right sheet To put things in perspective I have approx 300 cells on each sheet to deal with. The same cells on each sheet are used for storing the data I work with. Can anyone help? -- Dave Peterson . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a single cmdButton to perform different tasks
Share the code you tried and explain what happened when it failed.
Roger on Excel wrote: Dear Dave, I tried this, but it doesnt seem to work. What do you think? Roger "Dave Peterson" wrote: Create a variable that's outside any of the procedures in your userform module: Option Explicit Option compare text 'maybe Dim WhichSheet as string Private Sub button1_click() whichsheet = "sheetname associated with button #1" 'do the work End sub Private Sub button2_click() whichsheet = "sheetname associated with button #2" 'do the work End sub ..... Then in the button that returns the values... Private Sub WriteTheValsBtn_Click() if whichsheet = "" then 'this shouldn't happen except when you're testing <vbg msgbox "something bad happened!" exit sub end if with activeworkbook.worksheets(whichsheet) .range("a1").value = me.textbox1.value ... end with end sub Untested, uncompiled. Watch for typos! Roger on Excel wrote: [Excel 2003] I have a user form with text boxes. I populate the text boxes using the following type of code: Private Sub CommandSheet1Get_Click() Dim ws As Worksheet Set ws = Worksheets("Sheet1") Me.txtname.Value = ws.Range("A1").Value End Sub I can edit the text boxes and then update the spreadsheet with the following code in another command button Private Sub cmdSendtoSheet1_Click() Dim ws As Worksheet Set ws = Worksheets("Sheet1") ws.Range("A1").Value = Me.txtname.Value End Sub Using this approach I can use a number of command buttons to pull data to the text boxes from different sheets 1,2,3....10. However, here is my question/problem. Although I want to have separate buttons for pulling the data to the textboxes, I want to use just a single button for sending the edited data back to the sheets. So what I would need is for the code to keep track of which sheet the data came from and when the button is pressed, would access the suitable subroutine to return the data to the right sheet To put things in perspective I have approx 300 cells on each sheet to deal with. The same cells on each sheet are used for storing the data I work with. Can anyone help? -- Dave Peterson . -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a single cmdButton to perform different tasks
Dear Dave,
I got the code to work as you described - thankyou so much - it works beautifully All the best, Roger "Dave Peterson" wrote: Share the code you tried and explain what happened when it failed. Roger on Excel wrote: Dear Dave, I tried this, but it doesnt seem to work. What do you think? Roger "Dave Peterson" wrote: Create a variable that's outside any of the procedures in your userform module: Option Explicit Option compare text 'maybe Dim WhichSheet as string Private Sub button1_click() whichsheet = "sheetname associated with button #1" 'do the work End sub Private Sub button2_click() whichsheet = "sheetname associated with button #2" 'do the work End sub ..... Then in the button that returns the values... Private Sub WriteTheValsBtn_Click() if whichsheet = "" then 'this shouldn't happen except when you're testing <vbg msgbox "something bad happened!" exit sub end if with activeworkbook.worksheets(whichsheet) .range("a1").value = me.textbox1.value ... end with end sub Untested, uncompiled. Watch for typos! Roger on Excel wrote: [Excel 2003] I have a user form with text boxes. I populate the text boxes using the following type of code: Private Sub CommandSheet1Get_Click() Dim ws As Worksheet Set ws = Worksheets("Sheet1") Me.txtname.Value = ws.Range("A1").Value End Sub I can edit the text boxes and then update the spreadsheet with the following code in another command button Private Sub cmdSendtoSheet1_Click() Dim ws As Worksheet Set ws = Worksheets("Sheet1") ws.Range("A1").Value = Me.txtname.Value End Sub Using this approach I can use a number of command buttons to pull data to the text boxes from different sheets 1,2,3....10. However, here is my question/problem. Although I want to have separate buttons for pulling the data to the textboxes, I want to use just a single button for sending the edited data back to the sheets. So what I would need is for the code to keep track of which sheet the data came from and when the button is pressed, would access the suitable subroutine to return the data to the right sheet To put things in perspective I have approx 300 cells on each sheet to deal with. The same cells on each sheet are used for storing the data I work with. Can anyone help? -- Dave Peterson . -- Dave Peterson . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a single cmdButton to perform different tasks
Glad it worked.
Roger on Excel wrote: Dear Dave, I got the code to work as you described - thankyou so much - it works beautifully All the best, Roger "Dave Peterson" wrote: Share the code you tried and explain what happened when it failed. Roger on Excel wrote: Dear Dave, I tried this, but it doesnt seem to work. What do you think? Roger "Dave Peterson" wrote: Create a variable that's outside any of the procedures in your userform module: Option Explicit Option compare text 'maybe Dim WhichSheet as string Private Sub button1_click() whichsheet = "sheetname associated with button #1" 'do the work End sub Private Sub button2_click() whichsheet = "sheetname associated with button #2" 'do the work End sub ..... Then in the button that returns the values... Private Sub WriteTheValsBtn_Click() if whichsheet = "" then 'this shouldn't happen except when you're testing <vbg msgbox "something bad happened!" exit sub end if with activeworkbook.worksheets(whichsheet) .range("a1").value = me.textbox1.value ... end with end sub Untested, uncompiled. Watch for typos! Roger on Excel wrote: [Excel 2003] I have a user form with text boxes. I populate the text boxes using the following type of code: Private Sub CommandSheet1Get_Click() Dim ws As Worksheet Set ws = Worksheets("Sheet1") Me.txtname.Value = ws.Range("A1").Value End Sub I can edit the text boxes and then update the spreadsheet with the following code in another command button Private Sub cmdSendtoSheet1_Click() Dim ws As Worksheet Set ws = Worksheets("Sheet1") ws.Range("A1").Value = Me.txtname.Value End Sub Using this approach I can use a number of command buttons to pull data to the text boxes from different sheets 1,2,3....10. However, here is my question/problem. Although I want to have separate buttons for pulling the data to the textboxes, I want to use just a single button for sending the edited data back to the sheets. So what I would need is for the code to keep track of which sheet the data came from and when the button is pressed, would access the suitable subroutine to return the data to the right sheet To put things in perspective I have approx 300 cells on each sheet to deal with. The same cells on each sheet are used for storing the data I work with. Can anyone help? -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I perform multiple formating within a single line of text? | Excel Worksheet Functions | |||
CmdButton on worksheet1 missing | Excel Discussion (Misc queries) | |||
Add to CmdButton | Excel Programming | |||
Automation of Excel to perform simple tasks on many workbooks. | Excel Programming | |||
Can I perform tasks on a closed Workbook | Excel Programming |