mark hansen
Well-known Member
- Joined
- Mar 6, 2006
- Messages
- 534
- Office Version
- 2016
- Platform
- Windows
I have the following code on 100 sheets along with 5 buttons to pick up a list of questions and answers from certian cells and put it in the clipboard for later pasting. Each of the 100 sheets are basically the same, except the questions and answers are different. (sorry, I don't know why the code is in different colors)
I was wondering if I can put the code on one place (instead of the same code on 100 pages) and have the 5 buttons on each page run the code BUT pick up the questions on the sheet they are on.
As each page in the VBE has the same code, I was hoping I can do something a bit different to reduce the overall amount of code and perhaps the excel file can be smaller.
(The worksheet selection change event allows certain cells to be clickable and act like buttons to insert text as the answer. I did this to reduce the amount of buttons drastically and the file size.
Can I put this code in the "ThisWorkbook" area and somehow reference the current sheet when the button is clicked so it uses the current sheet's questions? If so, how would I modify the code so button 1 does the same thing, but with the sheet its on?
Thanks for any ideas?
Mark
I was wondering if I can put the code on one place (instead of the same code on 100 pages) and have the 5 buttons on each page run the code BUT pick up the questions on the sheet they are on.
As each page in the VBE has the same code, I was hoping I can do something a bit different to reduce the overall amount of code and perhaps the excel file can be smaller.
(The worksheet selection change event allows certain cells to be clickable and act like buttons to insert text as the answer. I did this to reduce the amount of buttons drastically and the file size.
Can I put this code in the "ThisWorkbook" area and somehow reference the current sheet when the button is clicked so it uses the current sheet's questions? If so, how would I modify the code so button 1 does the same thing, but with the sheet its on?
PHP:
Private Sub CommandButton1_Click()
Call copy_to_so
End Sub
Private Sub CommandButton2_Click()
Call clear
End Sub
Private Sub CommandButton3_Click()
Range("D111").Value = Range("D110").Value
Range("D111").Copy
Application.WindowState = xlMinimized
End Sub
Private Sub CommandButton4_Click()
Call copy_to_so
End Sub
Private Sub CommandButton5_Click()
Call clear
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Rem Determine if user has selected a cell in the range of interest
If Not Intersect(Target, Range("G5:K54")) Is Nothing Then
If Len(ActiveCell.Value) = 0 Then
Exit Sub
End If
With Target
' MsgBox (ActiveCell.Column)
If ActiveCell.Column = 7 Then
Range("E" & ActiveCell.Row) = Range("S" & ActiveCell.Row)
End If
If ActiveCell.Column = 8 Then
Range("E" & ActiveCell.Row) = Range("V" & ActiveCell.Row)
End If
If ActiveCell.Column = 9 Then
Range("E" & ActiveCell.Row) = Range("Y" & ActiveCell.Row)
End If
If ActiveCell.Column = 10 Then
Range("E" & ActiveCell.Row) = Range("AB" & ActiveCell.Row)
End If
If ActiveCell.Column = 11 Then
Range("E" & ActiveCell.Row) = Range("AE" & ActiveCell.Row)
End If
End With
' ActiveSheet.Protect
End If
End Sub
Thanks for any ideas?
Mark