View Full Version : Function isn't working on button click
I added a button to a sheet, but for whatever reason I can't call a function inside of it.
Button code:
Sub AddLabor_Click()
addRow (range("B7:B8"))
End Sub
Function that I need to run:
Private Function addRow(r As range)
Dim cell As range
For Each cell In r
If IsEmpty(cell) Then
Rows(cell.Row).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Exit For
End If
Next cell
End Function
If I just paste the function code into button event handler, then everything works, but I need this code to be reusable. What's the problem here?
I'm getting "object required" error.
I also tried something like this:
Private Function test()
Debug.Print "test"
End Function
Sub AddLabor_Click()
test()
End Sub
But this one fails completely, saying "=" is expected.
Paul_Hossler
01-06-2014, 04:32 PM
but I need this code to be reusable. What's the problem here?
The Private on the Function
Public
Optional. Indicates that the Function procedure is accessible to all other procedures in all modules (http://office.microsoft.com/client/helppreview14.aspx?AssetId=HV080006915&lcid=1033&NS=EXCEL%2EDEV&Version=14&CTT=5&origin=HV080007929). If used in a module that contains an Option Private, the procedure is not available outside the project (http://office.microsoft.com/client/helppreview14.aspx?AssetId=HV080006915&lcid=1033&NS=EXCEL%2EDEV&Version=14&CTT=5&origin=HV080007929).
Private
Optional. Indicates that the Function procedure is accessible only to other procedures in the module where it is declared.
Try something like this
Option Explicit
Option Private Module
Function addRow(r As Range)
Dim cell As Range
For Each cell In r
If IsEmpty(cell) Then
Rows(cell.Row).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Exit For
End If
Next cell
End Function
Paul
Aflatoon
01-07-2014, 02:41 AM
Your button code is incorrect - you must remove the parentheses around the range parameter:
Sub AddLabor_Click()
addRow range("B7:B8")
End Sub
Aussiebear
01-07-2014, 05:28 AM
Okay I'll put my hand up to be shot at: Can we have a row calling for the function "addRow" and a range address on the same line? And, would the target address be better served inside the function?
Aflatoon
01-07-2014, 05:48 AM
I'm afraid I don't understand the question. Care to elaborate?
Paul_Hossler
01-07-2014, 06:19 AM
Okay I'll put my hand up to be shot at: Can we have a row calling for the function "addRow" and a range address on the same line? And, would the target address be better served inside the function?
Well, I wouldn't have done it that way either, so at least I'm in good company
To be honest I really wasn't sure what the OP was trying to do, but I thought I could at least address the issue about not seeing the function
Paul
Paul, Private is the correct scope, I don't need this accessible to outside modules.
Aflatoon, Thank you, that was exactly it!!! Now it works.
mikerickson
01-07-2014, 07:58 AM
I noticed that, under some common conditions (if the argument being passed isn't on the ActiveSheet), the function might insert a row on the wrong sheet. Try
cell.EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Paul_Hossler
01-07-2014, 12:56 PM
Paul, Private is the correct scope, I don't need this accessible to outside modules.
Sorry -- made bad assumption that 'addRow' was in a standard module to be reusable in other handlers
Paul
Aussiebear
01-07-2014, 03:31 PM
I'm afraid I don't understand the question. Care to elaborate?
The original code has a line "addRow range("B7:B8")". My understanding is that "addRow" is calling the function, and the range("B7:B8") is the address suggested for the inserted row. Last night the code didn't make any real sense, and again looking at it this morning I still think its a long way off. Since we know the location for the insertion why are we calling for a function when with just a couple of lines we can have the one sub
Sub AddLabor_Click()
With Range("B7:B8)
If IsEmpty(Cell) Then
Rows(Cell.Row).Insert Shift:=xlDown, CopyOrigin:= xlFormatFromLeftOrAbove
End If
End With
End Sub
Aflatoon
01-07-2014, 03:55 PM
Because then you might have to repeat the same code for multiple buttons. ;)
Much easier to have a function that takes a range as a parameter.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.