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?

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

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
.

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

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
.

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