PDA

View Full Version : Solved: Binding Macros to Buttons



alanpreece
04-17-2010, 05:35 AM
This is probably extremely simple for most of the users here. But I've been trying to understand this for some time and many searches have resulted in nothing.

Essentially I have a workbook that has data on sheet1 and a macro that searches and displays these results on another sheet (named "Search Results") then on this resulting page I wanted to put a button at the top that when pressed would erase the page and return to Sheet1.

Heres the code I'm trying to use...

Sub CreateButton()

Dim Obj As Object
Dim Code As String

Sheets("Search Results").Select


'create button
Set Obj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, Left:=4, Top:=4, Width:=120, Height:=20)
Obj.Name = "delete"
'buttonn text
ActiveSheet.OLEObjects(1).Object.Caption = "Back/Delete Search"

'macro text
Code = "Sub delete()" & vbCrLf
Code = "ActiveWindow.SelectedSheets.delete" & vbCrLf
Code = "End Sub" & vbCrLf
End Sub


It would also be great if there was a way where when the workbook was closed it automatically deleted "Search Results" silently.

I hope some of this makes sense, I'm obviously new to this VB programming, but I find it very interesting (if somewhat confusing).

Hope someone can help out, thanks if you can and thanks if you just took the time to take a look.
:hi:

lucas
04-17-2010, 05:40 AM
Is the sheet "search results" created by the search code?

The reason I ask is that I don't understand why you want to create the button with code.

alanpreece
04-17-2010, 05:45 AM
I should have stated this before, but yes the "Search Results" page is created by the search code (and overwrites any previous page with that name).

Else I could just design the button like I did the main "Sheet1" page.

I know that people could just delete the "Search Results" manually, but the people who will be using the workbook are not all very computer literate.

lucas
04-17-2010, 06:22 AM
Alan, adding controls on the fly is a little complicated as you have discovered.

Could you possibly post your workbook without any real data so I could look at the possibility of using a hidden template file with the button already on it if that sounds like a solution you would consider.

click on go advanced and then scroll down and look for a button that says manage attachments.

alanpreece
04-17-2010, 06:32 AM
Here it is, I have to say I'm overjoyed that I have you to help me out with this problem. I was beginning to wonder if I were ever going to get anywhere with it.

I didn't think it would be anywhere near this complicated...

mdmackillop
04-17-2010, 06:38 AM
My KB item here (http://www.vbaexpress.com/kb/getarticle.php?kb_id=780) uses an add-in to create a search sheet. It could be modified to add a button to delete the sheet but it seems unnecceesary. You could more easily add a Workbook event to delete the sheet on closing/opening.

alanpreece
04-17-2010, 06:51 AM
That certainly would be a good way of doing itmdmackillop, but I was thinking of people who might want to re-search. I figured a "back to the start" button might be a clean way of resetting the workbook for people, especially for those who are less computer literate.

Thanks for the link to your search function, I'll certainly study it (and perhaps use it, with your permission). I've developed quite an interest in VB programming, even though I am a NOOB at it.

(Edit: actually after looking at it that might be the code I'm actually using. It looks very similar, though there are a few changes. Perhaps the person who was helping me before with it frequented this forum.)

Out of interest what would the event to delete the "Search Results" page look like?

mdmackillop
04-17-2010, 06:53 AM
I see now you already found it!

mdmackillop
04-17-2010, 06:59 AM
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sh
If Saved = True Then
On Error Resume Next
Set sh = Sheets("Search Result")
If Not sh Is Nothing Then
Application.DisplayAlerts = False
sh.delete
ActiveWorkbook.Save
End If
Application.DisplayAlerts = True
End If
End Sub

alanpreece
04-17-2010, 07:04 AM
I think we posted simultaneously then mdmackillop.

How would I go about altering that code to do what I had in mind? This is kind of what I've been trying to do with poor (in other words "no") results. I added a CreateButton sub and then started in a new macro (mainly to try and not alter what my friend had supplied for me to much).

edit: Posted together again there mate, thanks for that code snippet. I'll take a look at it.

lucas
04-17-2010, 07:20 AM
Here's my stab at it Alan. It has a hidden sheet that is the template and if they accidentally try to search twice without deleting the sheet it will delete it and make a new one from the template.

Also deletes the worksheet on close if they forget.

alanpreece
04-17-2010, 07:32 AM
lucas, you are a god among men and if it were physiologically possible I would consider having your children...

Now I've got to take a really good look at it and attempt to try to understand what you did and how you did it.

Does anyone know of any good learning resources for VB? Apart from this excellent site of course.

A recommendation of any books would be very cool, as I want to understand this business and hopefully not get myself in a position again where I am completely lost.

Thanks again lucas, mdmackillop, I truly appreciate the help here.

lucas
04-17-2010, 07:40 AM
Na, just a novice myself. Malcolm is among our true legends here.

I had just been in the same corner before so I had an idea or two that someone else had pointed me to previously.

You can mark your thread solved using the thread tools at the top of the page.

That keeps others from reading the entire thread just to find out that it's been resoved.

What I did basically was instead of creating a new sheet, I replaced your code for that and copied the template sheet and renamed it.

The template sheet has the button on it and the code within the code module for the sheet.

The before close event just checks to see if the sheet exists and if it does, it deletes it. It uses the function in the shtExistsFunction Module.

Just before the code to copy the template I also use that same function to check and see if the sheet exists. If it does, it gets deleted first.

mdmackillop
04-17-2010, 07:43 AM
As we are already writing code using code, we can add the button click code to the new sheet. The actual delete code is "permanent" underneath AddSheetCode as shown below

Sub AddSheetCode()
'Thanks to Dragontooth
Dim strCode As String
Dim strCode2 As String
Dim FWord As String
Dim WB As Workbook
Dim sh
Dim I As Integer
Set WB = ActiveWorkbook

strCode = "Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)" & vbCr _
& "If Sh.Name = " & Chr(34) & "Search Results" & Chr(34) & " Then" & vbCr _
& "If Target.Address = " & Chr(34) & "$B$1" & Chr(34) & " Then" & vbCr _
& "FindAll Target.Text, " & Chr(34) & "False" & Chr(34) & vbCr _
& "Cells(1,2).Select" & vbCr _
& "End if" & vbCr _
& "End if" & vbCr _
& "End Sub"

'Write code to ThisWorkbook module
FWord = "ThisWorkbook"
For I = 1 To WB.VBProject.VBComponents.Count
If WB.VBProject.VBComponents.Item(I).Name = FWord Then
Exit For
End If
Next

If Not WB.VBProject.VBComponents.Item(I).CodeModule Is Nothing Then
If Not WB.VBProject.VBComponents.Item(I).CodeModule.Find("Workbook_SheetChange", 1, 1, 100, 100) Then
WB.VBProject.VBComponents.Item(I).CodeModule.AddFromString (strCode)
End If
End If

'Delete sheet code added to Search Results
strCode2 = "Private Sub CommandButton1_Click()" & vbCr _
& "Sheets(1).Activate" & vbCr _
& "Call DelSheet" & vbCr _
& "End Sub"

For I = 1 To WB.VBProject.VBComponents.Count
If Sheets("Search Results").CodeName = WB.VBProject.VBComponents.Item(I).Name Then
Exit For
End If
Next

If Not WB.VBProject.VBComponents.Item(I).CodeModule Is Nothing Then
WB.VBProject.VBComponents.Item(I).CodeModule.AddFromString (strCode2)
End If

Set WB = Nothing

End Sub

Sub DelSheet()
Application.DisplayAlerts = False
Sheets("Search Results").delete
Application.DisplayAlerts = True
End Sub

mdmackillop
04-17-2010, 08:45 AM
Thanks Steve. :blush

For information; I would generally use Steve's template solution. The Add-In was intended to add to any workbook to allow a specific column search. Your use looks more specific, so the template seems more appropriate.

alanpreece
04-17-2010, 11:19 AM
Thanks guys.

A very interesting solution and something I had not even known was possible, this is something I truly do intend to learn something more about.

I'm going to buy myself some books and start studying I think...:friends: