Using a single cmdButton to perform different tasks

R

Roger on Excel

[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?
 
D

Dave Peterson

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!
[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?
Click to expand...
 
R

Roger on Excel

Dear Dave,

I tried this, but it doesnt seem to work. What do you think?

Roger

Dave Peterson said:
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!
[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?
Click to expand...
Click to expand...
 
D

Dave Peterson

Share the code you tried and explain what happened when it failed.
Dear Dave,

I tried this, but it doesnt seem to work. What do you think?

Roger

Dave Peterson said:
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!
[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?
Click to expand...
Click to expand...
Click to expand...
 
R

Roger on Excel

Dear Dave,

I got the code to work as you described - thankyou so much - it works
beautifully


All the best,

Roger



Dave Peterson said:
Share the code you tried and explain what happened when it failed.
Dear Dave,

I tried this, but it doesnt seem to work. What do you think?

Roger

Dave Peterson said:
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?
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

Glad it worked.
Dear Dave,

I got the code to work as you described - thankyou so much - it works
beautifully

All the best,

Roger

Dave Peterson said:
Share the code you tried and explain what happened when it failed.
Dear Dave,

I tried this, but it doesnt seem to work. What do you think?

Roger

:

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?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Coding Help for Beginner 0
Code copies twice...? 13
Calling a subroutine - how to? 4
Creating a Search using a combo box and then creating a report. 4
revise CONCATENATE code 2
Code execution is correct but generates Error :The file you aretrying to open is in a different form 3
Userform List boxes - editing - then option to save changes (or no 3
CONCATENATE code 2

Top