seattletimebandit
Board Regular
- Joined
- Apr 11, 2013
- Messages
- 69
I don't think my Descriptive Title is as clear as I need it to be, but...
If you've seen this post on another forum, yup, I did. Just covering my bases...
I have a workbook that has a worksheet that runs a macro to transpose a range of data (Sub TransposeLabData). At the end of the vb code, I have the transposed data moved to a new workbook ("New Table") with a name for the worksheet (named from user input). Works great, saves it to my Documents folder, no issues.
I have another bit of code (Sub GreaterThanCULs) that scans the transposed range of data and bolds and/or highlights cells based on criteria. That works well if I run the macro by selecting "Macros" in the "Developer" tab of the Excel ribbon.
BUT, I want to add a Command Button to run "Sub GreaterThanCULs" in the newly created worksheet. I successfully was able to have the new sheet open with the transposed data and the Command Button named "Bold Hits & Highlight CULs". However, when clicking the button, nothing happens. It doesn't call the "Sub GreaterThanCULs" code.
Is it because the code is in the module "Sub TransposeLabData" in the first worksheet? If that's the case, how do I call the "Sub GreaterThanCULs" code in the original workbook from the newly created worksheet?
Am I making any sense?
When trying to figure out how to do this, I searched lots of forums and found the following "Set Obj" snippet used to add the command button and call the code.
Thanks in advance! All of you that help us rookie coders really appreciate you...
And if you need me to upload a copy of the TransposeData.xlsm workbook, I will do so.
The bit that creates the button and new workbook:
The code to call:
NOTE: this bit below when allowed to run, has Excel tell me that the "VB Project cannot be saved in macro-free workbooks". If it's hidden, like in the above code, I don't get the "macro-free" warning, but the code, of course, still doesn't run.
So I'm guessing that I need to have my new worksheet be formatted as "New Table.xlsm"? Then will the code work? How do I save the new worksheet with the macro friendly .xlsm extension? Haven't been able to figure that out or find it on the web.
If you've seen this post on another forum, yup, I did. Just covering my bases...
I have a workbook that has a worksheet that runs a macro to transpose a range of data (Sub TransposeLabData). At the end of the vb code, I have the transposed data moved to a new workbook ("New Table") with a name for the worksheet (named from user input). Works great, saves it to my Documents folder, no issues.
I have another bit of code (Sub GreaterThanCULs) that scans the transposed range of data and bolds and/or highlights cells based on criteria. That works well if I run the macro by selecting "Macros" in the "Developer" tab of the Excel ribbon.
BUT, I want to add a Command Button to run "Sub GreaterThanCULs" in the newly created worksheet. I successfully was able to have the new sheet open with the transposed data and the Command Button named "Bold Hits & Highlight CULs". However, when clicking the button, nothing happens. It doesn't call the "Sub GreaterThanCULs" code.
Is it because the code is in the module "Sub TransposeLabData" in the first worksheet? If that's the case, how do I call the "Sub GreaterThanCULs" code in the original workbook from the newly created worksheet?
Am I making any sense?
When trying to figure out how to do this, I searched lots of forums and found the following "Set Obj" snippet used to add the command button and call the code.
Thanks in advance! All of you that help us rookie coders really appreciate you...
And if you need me to upload a copy of the TransposeData.xlsm workbook, I will do so.
The bit that creates the button and new workbook:
Code:
With Activesheet
Set Obj = .OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, Left:=400, _
Top:=400, Width:=200, Height:=35)
Obj.Name = "GreaterThanCULs" '<< name must match code below...
Obj.Object.Caption = "Bold Hits & Highlight CULs"
Code = "Sub GreaterThanCULs()" & vbCrLf & _
" Call GreaterThanCULs" & vbCrLf & _
"End Sub"
'With .Parent.VBProject.VBComponents(.CodeName).CodeModule
' .insertlines .CountOfLines + 1, Code
'End With
End With
Activesheet.Move 'Move newly created "Transposed Data" worksheet to a new workbook
ActiveWorkbook.SaveAs "New Table" 'Save new workbook (should save to default "Documents" folder on User's computer
With activeworkboook.NewTable
End With
The code to call:
Code:
Sub GreaterThanCULs()
Application.ScreenUpdating = False
Dim lastColumn As Integer
Dim c1 As Range
Dim c2 As Range
For Each c1 In Range("b4:b100")
If c1 <> "" And IsNumeric(c1) Then
lastColumn = Activesheet.Cells(c1.Row, Columns.Count).End(xlToLeft).Column
For Each c2 In Range(Cells(c1.Row, 3), Cells(c1.Row, lastColumn))
If c2 <> "" And IsNumeric(c2) And c2 >= c1 Then
c2.Interior.ColorIndex = 28
End If
Next c2
End If
Next c1
For Each c1 In Range("b4:b100")
If c1 = "" Or IsNumeric(c1) Then
lastColumn = Activesheet.Cells(c1.Row, Columns.Count).End(xlToLeft).Column
For Each c2 In Range(Cells(c1.Row, 3), Cells(c1.Row, lastColumn))
If Right(c2.Value, 1) = "U" Then
c2.Font.Bold = False
Else: c2.Font.Bold = True
End If
Next c2
End If
Next c1
Application.ScreenUpdating = True
End Sub
NOTE: this bit below when allowed to run, has Excel tell me that the "VB Project cannot be saved in macro-free workbooks". If it's hidden, like in the above code, I don't get the "macro-free" warning, but the code, of course, still doesn't run.
So I'm guessing that I need to have my new worksheet be formatted as "New Table.xlsm"? Then will the code work? How do I save the new worksheet with the macro friendly .xlsm extension? Haven't been able to figure that out or find it on the web.
Code:
With .Parent.VBProject.VBComponents(.CodeName).CodeModule
.insertlines .CountOfLines + 1, Code
End With