VBA Help Please

Positron · Post by **Positron** » Sun Apr 27, 2008 2:59 pm

What I want to do is to call(run) a subroutine from another subroutine that is on a different sheet.

jeffslot · Post by **jeffslot** » Sun Apr 27, 2008 3:12 pm

Positron,

On Sheet two I made a sub procedure similar to below:

Public Sub SayHello()
MsgBox "Hello World"
End Sub

Then behind my command button on sheet one I used:

Private Sub CommandButton1_Click()
Call Sheet2.SayHello
End Sub

I hope this is what you meant. This works for me.

What I want to do is to call(run) a subroutine from another subroutine that is on a different sheet.

Positron · Post by **Positron** » Sun Apr 27, 2008 3:20 pm

Ok. I got your example to work but when I implement it to mine it gives me the "Object Required" error...

jeffslot · Post by **jeffslot** » Sun Apr 27, 2008 3:26 pm

Is it possible you aren't calling the sheet by the right name?

Positron · Post by **Positron** » Sun Apr 27, 2008 3:31 pm

Thank you for your help. I think that's what it was... I just added Worksheets("Sheet1").Activate, plus your example and it did it.... WOW... Thank you, I was getting very frustrated.

jeffslot · Post by **jeffslot** » Sun Apr 27, 2008 3:40 pm

Positron · Post by **Positron** » Sun Apr 27, 2008 10:48 pm

I hit another snag.... I need a macro that removes a border of a cell.

jeffslot · Post by **jeffslot** » Mon Apr 28, 2008 4:29 am

Positron,

This should be pretty easy, & I'll be more than happy to help you. Did you know that you can 'Record' a macro & then simply look at the generated code?

Let me know.

Jeff

Positron · Post by **Positron** » Mon Apr 28, 2008 10:05 am

I am not quite sure what the record feature does. How do I use it correctly? How will it help me? Thanks again.

jeffslot · Post by **jeffslot** » Mon Apr 28, 2008 11:46 am

Well If I

* select Tools-->Macro-->Record New Macro from the tool bar &
* click on OK to start the recording.
* Select any cell
* Right-click & select Format Cells from the pop up menu.
* Click on the Border Tab
* Click on the NONE preset.
* Click on OK to close the format cells dialog.
* Go to Tools-->Macro-->Stop Recording
* Go to the visual basic editor
* Double-click on the Module1 I can see code similar to:

Code: Select all

Sub Macro1()

Selection.Borders(xlDiagonalDown).Linestyle =  xlNone
Selection.Borders(xlDiagonalUp).Linestyle =  xlNone
Selection.Borders(xlEdgeLeft).Linestyle =  xlNone
Selection.Borders(xlEdgeTop).Linestyle =  xlNone
Selection.Borders(xlEdgeBottom).Linestyle =  xlNone
Selection.Borders(xlEdgeRight).Linestyle =  xlNone
Selection.Borders(xlInsideVertical).Linestyle =  xlNone
Selection.Borders(xlInsideHorizontal).Linestyle =  xlNone

End Sub

I end up with the code that can remove the border of a cell. Of course you could always spruce up this code if you wanted, maybe use a with statement to cut down on redundant code, but it should still get the job done.

Positron · Post by **Positron** » Mon Apr 28, 2008 1:38 pm

Cool! The record feature should help me with most of my future problems. Thanks for all your help.