Here is an extremely basic concept, that is worth demoing. Let's assume that you have an Add-on created to the point where you can show/hide your frame with a minimap button or slash command in the chat window. Now you want to tie into some event handlers for your frame widget.

First, you will need to create (2) functions in your Lua file:
function MyFrame_OnShow(self)
    PlaySound("igQuestLogOpen");
end

function MyFrame_OnHide(self)
    PlaySound("igQuestLogClose");
end
The actual function names don't matter much here. You could have gone with "MyFrame.OnShow(self)", "MyFrame_DoOnShowStuff(self)", or a number of other versions. As long as you match up with the right function name in the next section, you'll be fine.

The other half of this is calling the "SetScript()" function in the Lua code where you are creating your frame. Most Add-ons have a function that does nothing but create the frame and populate it with controls and artwork. So, sometime after calling "CreateFrame()" and before you call "Show()", you'll want to insert the following two lines to call your event handling functions:
f:SetScript("OnShow",MyFrame_OnShow)
f:SetScript("OnHide",MyFrame_OnHide)
Your frame will now make pretty quest log noises when it appears and disappears from the user's view. Make sure you pick something soothing and easy to listen to or it will get annoying very quickly.