Using Object & Worksheet names as variables

Let's see if I can ask this is a way that makes sense:

I have a worksheet with nine image boxes on it  named Image1-9. I have 
identical code for each image box that handles photo insertion and deletion. 
I think I can clean up the code by having the image_click() procedure call a 
sub function, rather than duplicate the image handling code nine times for 
each imagebox click event.

Question #1:

I can collect the worksheet name as:

Dim SheetID as String

SheetID = ActiveSheet.Name

How do I collect the name (Image1) from the the object that triggers the 
click event?

Question #2:

How do I pass these variables to my photo handling code, and construct the 
code to reference these variables? 

Here's my current photo handling code which I want to make into a sub 
function to call with the image click event:

Private Sub Image1_Click()

Dim NewImg As Long
Dim DelImg As Long

NewImg = MsgBox("Insert New Photo?", vbYesNoCancel)
    If NewImg = vbYes Then
        FileToOpen = Application.GetOpenFilename( _
            "All Files (*.jpg),*.jpg, All Files (*.bmp),*.bmp")
        If FileToOpen <> False Then
            Worksheets("Sheet1").OLEObjects("Image1").Object.Picture _
                = LoadPicture(FileToOpen)
            With Image1
                .BackColor = &H80000005
                .BorderStyle = fmBorderStyleNone
            End With
        End If
    ElseIf NewImg = vbNo Then
        If Worksheets("Sheet1").OLEObjects("Image1"). _
                      Object.Picture Is Nothing Then
                      GoTo Skip
            End If
        DelImg = MsgBox("Remove Current Photo?", vbYesNo)
            If DelImg = vbYes Then
                Worksheets("Sheet1").OLEObjects("Image1").Object.Picture = 
LoadPicture("")
                With Image1
                    .BackColor = &H8000000F
                    .BorderStyle = fmBorderStyleSingle
                End With
            ElseIf DelImg = vbNo Then
            End If
    ElseIf NewImg = vbCancel Then
        End If
Skip:
    
End Sub

Thanks!

0
Utf
4/9/2010 5:29:01 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
984 Views

Similar Articles

[PageSpeed] 38

You don't need the worksheet, as the image will be associated with that.

In the caller, you just have to use the same name as the event.

SO, in a standard module add

Public Sub HandleIMage(mImage As Object)
Dim NewImg As Long
Dim DelImg As Long
Dim FileToOpen As Variant

    NewImg = MsgBox("Insert New Photo?", vbYesNoCancel)
    If NewImg = vbYes Then
        FileToOpen = Application.GetOpenFilename( _
            "All Files (*.jpg),*.jpg, All Files (*.bmp),*.bmp")
        If FileToOpen <> False Then
            mImage.Object.Picture = LoadPicture(FileToOpen)
            With mImage
                .BackColor = &H80000005
                .BorderStyle = fmBorderStyleNone
            End With
        End If
    ElseIf NewImg = vbNo Then
        If Not mImage.Object.Picture Is Nothing Then

            DelImg = MsgBox("Remove Current Photo?", vbYesNo)
            If DelImg = vbYes Then
                mImage.Object.Picture = LoadPicture("")
                With mImage
                    .BackColor = &H8000000F
                    .BorderStyle = fmBorderStyleSingle
                End With
            ElseIf DelImg = vbNo Then
            End If
        End If
    End If
End Sub

Then the image click events will be like so

Private Sub Image1_Click()
    Call HandleIMage(Image1)
End Sub



-- 

HTH

Bob

"mooresk257" <mooresk257@discussions.microsoft.com> wrote in message 
news:586DE7E6-5B84-4BE4-9B78-A3562D49DCEE@microsoft.com...
> Let's see if I can ask this is a way that makes sense:
>
> I have a worksheet with nine image boxes on it  named Image1-9. I have
> identical code for each image box that handles photo insertion and 
> deletion.
> I think I can clean up the code by having the image_click() procedure call 
> a
> sub function, rather than duplicate the image handling code nine times for
> each imagebox click event.
>
> Question #1:
>
> I can collect the worksheet name as:
>
> Dim SheetID as String
>
> SheetID = ActiveSheet.Name
>
> How do I collect the name (Image1) from the the object that triggers the
> click event?
>
> Question #2:
>
> How do I pass these variables to my photo handling code, and construct the
> code to reference these variables?
>
> Here's my current photo handling code which I want to make into a sub
> function to call with the image click event:
>
> Private Sub Image1_Click()
>
> Dim NewImg As Long
> Dim DelImg As Long
>
> NewImg = MsgBox("Insert New Photo?", vbYesNoCancel)
>    If NewImg = vbYes Then
>        FileToOpen = Application.GetOpenFilename( _
>            "All Files (*.jpg),*.jpg, All Files (*.bmp),*.bmp")
>        If FileToOpen <> False Then
>            Worksheets("Sheet1").OLEObjects("Image1").Object.Picture _
>                = LoadPicture(FileToOpen)
>            With Image1
>                .BackColor = &H80000005
>                .BorderStyle = fmBorderStyleNone
>            End With
>        End If
>    ElseIf NewImg = vbNo Then
>        If Worksheets("Sheet1").OLEObjects("Image1"). _
>                      Object.Picture Is Nothing Then
>                      GoTo Skip
>            End If
>        DelImg = MsgBox("Remove Current Photo?", vbYesNo)
>            If DelImg = vbYes Then
>                Worksheets("Sheet1").OLEObjects("Image1").Object.Picture =
> LoadPicture("")
>                With Image1
>                    .BackColor = &H8000000F
>                    .BorderStyle = fmBorderStyleSingle
>                End With
>            ElseIf DelImg = vbNo Then
>            End If
>    ElseIf NewImg = vbCancel Then
>        End If
> Skip:
>
> End Sub
>
> Thanks!
> 


0
Bob
4/9/2010 5:46:21 PM
Reply:

Similar Artilces:

Add an Active Directory Object to Organizational Unit !!!!
Hi everyBody , I want to add an Active Directory Object (User Acount , Group , Compturer...) to an OU by using PowerShell , and i don't know How , any help will be apreciate . thanks . use the redirusr or redircmp from microsoft. It will always go to that OU. "mamhil" <mohamedtawfik@hotmail.com> wrote in message news:BAC9EB20-A4B6-466B-96A9-587B645A18F5@microsoft.com... > Hi everyBody , I want to add an Active Directory Object (User Acount , > Group , Compturer...) to an OU by using PowerShell , and i don't know How > , any help will be ...

Using mouse wheel in VB editor
Just tried using the mouse wheel to scroll within visual basic editor in excel but it wont let me. How can I turn this feature on. I am using Excel 2003. Thanks in advance. This is a known problem with the latest MS mouse drivers. You can either install version 4 of the drivers (current version is 5), or use FreeWheel, and freeware program at http://www.geocities.com/SiliconValley/2060/freewheel.html . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "fullers" <fullers@discussions.microsoft.com> wrote in message ne...

Send to certain person using specific email account
I send an email to a certain receipient but don't want to use the default email account. Is it possible that when I send to them (plain text) , that it will choose the other account I want to use to send it? Please advise how I set this up. Thanks. I do not believe that this can be achieved automatically through standard use of Outlook as you need to manually specify the name of the account to send from if different than the default. However, it would be possible using the Outlook object model. An Addin would need to be developed that monitored each mail item prior to sending. If the d...

Use exchange from a trusted domain
Hi folks, I have domain a and domain b. Domain a is all set upp with exchange etc. Domain b was bought, and they don't have exchange. I have trust between the domains and file sharing and authentication are ok. What we want to do is, make users on domain b and let them use the exchange (on a seperate storage) on domain a. The users on domain b must logon to domain b, but need to have mailbox on domain a. Domain a are running on win2003 and exchange 2003. Domain are running on win2003 with forest and domain ready for exchange. Any advices? thanks in advance, - Bjarni Hi, Thi...

Renaming Partial File Names
Is it possible to rename part of a file name? (almost like a find and replace) My database pulls in the excel file names from a directory, with it's subfolders and contents as well. Every file begins with "Kay Form". Kay no longer works in our department, and I'd like to run a loop that replaces "Kay Form" with "Featured Track". I don't know how to do a partial replace though. Thank you in advance! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/201001/1 Checkout the Replace function. I...

Using Access 2003 on client to see Sharepoint 2007
Testing a Sharepoint environment and I can easily manipulate and run the database from Access 2007, but when I try to use Access 2003 it wants me to save the database to a location. The Access 2003 has the compatiblity pack, but it seems to not work when I download it. So the question is using Sharepoint 2007 and posting the Access 2007 format to it, can a user go to the sharepoint site and use a 2003 Access database that has the compatibility pack and open without having to download it? -- Message posted via http://www.accessmonster.com Send check for $500.00 to: David A Jenn...

VBA to put a copy of worksheet on the desktop 05-13-10
Hi all, In my workbook XYZ I have a sheet ABC. With a button on sheet DEF I can refresh sheet ABC. When the code finishes it job I want to add the actual date (short European notation dmyy) and time (f.i. 241110 16.31) to the name of the sheet (which becomes ABC 241110 16.31) and after that make a copy of that sheet in a separate workbook and put that workbook as an icon on the desktop of my computer. Is this possible? If so, please help me with the necessary code. Thanks in advance for your assistance. Jack Sons The Netherlands ...

Shortcut for inserting names?
Hi, I am building many formulas, each with many named cells as part of it. Currently I have to go to Insert | Name | Paste with the mouse each time I want to insert a name. I looked in Walkenbach's Excel Bible but couldn't find any mention of a keyboard shortcut or other shortcut. Is there one I could take advantage of? I've got Excel 2002. Thanks, Jamie ...

Adding a combo box to a worksheet
Hi all, I'm re-creating one of our paper forms in Excel and I'd like to add combo boxes to some blanks on the form to allow the user to choose a name from a list. I know a little about Excel formulas and no VB code at all...what's the idiot-proof way to do this? Thanks, Chris Hi Chris, The easiest way is to right-click within Excel in the toolbars area and select the "Forms" toolbar. Then Forms toolbar should then appear and could can select the "Combo Box" icon and click on that. If you can't tell which icon represents the Combo Box, just hover yo...

How Use ShellExecute with a CMemFile
Hello, I need to store files of various types (such as .doc, .xls, .jpg, .pdf, ..wmv, ...) in a database. Upon retrieval of such a file I have the file as data in memory in a CMemFile. I need to then "display" the file in the aproriate application. If I were to write the CMemFile to disk and create an actual file I could then, of course, use ShellExecute() to launch , say Word to display a .doc file. However, I would much prefer to avoid the overhead of writing the data to an actual file and then dealing with having to detect when the User is done and cleaning up the file I had ...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Indirect Method for Name Box Variable? #2
DARN The reason I want to do this is that I have to collect data on a dail basis but I have to display it on a graph on a weekly basis. I select the entire week of cells and give it a name like week52. The following week I have to select the entire week and name i week53. Everything is set up that I can just copy and the numbers will progres for whatever length of time I need to graph (months, years, etc) excep this. Maybe I'll just go back to using the range of cell numbers. Thanks guys -- Chris Brenna ----------------------------------------------------------------------- Chris Bre...

TEMP environment variable
Hi, Does somebody know what happens when I write in the 'TEMP' environment variable more than one directory, seperated by a ';' (For example 'C:\windows\temp ; d:\temp')? Does the OSs (98, 2000, XP) know to use BOTH directories as a temporary directories? Regards, Ram ...

Using INDIRECT within functions
I learned a bit from JLathman in a previous post but tried to use the same methodology with this formula (also learned from this Group to get the last entered value in a column) without much luck. The original formula was this with 2009 sheet being static: =LOOKUP(2,1/('2009'!H3:H65536<>""),'2009'!H3:H65536) Trying to make it dynamic I tried the following without success: =LOOKUP(INDIRECT("2,1/(" & O1 & "!$H$3:$H$65536<>"")," & O1 & "!$H$3:$H$65536)")) Did I miss something or are...

Sorting by file extension in a worksheet
Hello, I have an excel worksheet which has a list of file names from a directory and various stats about each file. I need to be able to sort them by the file extension. Eg. c:\documents\folderA\picture1.eps c:\documents\folderB\document.doc I would like to sort them by the ".eps" extension. I have tried using the Data,Sort menu and something like *?*.eps as the criteria. But am obvisouly missing something. Any help would be most appreciated. Thanks Karl You could use a helper column to extract just the extension then include this in your sort range and sort on the helper col...

How to use count for calls per hour
I am trying to create a pivot table and chart that will show me the # of calls per hour by day for a range of dates Aug 1 - Sept 19. My data looks like this where each line is the date and time of the call: How do I get a count of the # of the calls per hour by day? 8/1/08 12:48 AM 8/1/08 1:53 AM 8/1/08 2:36 AM 8/1/08 3:24 AM 8/1/08 3:37 AM 8/1/08 4:04 AM 8/1/08 4:44 AM 8/1/08 4:55 AM 8/1/08 4:58 AM 8/1/08 7:02 AM 8/1/08 7:28 AM 8/1/08 7:43 AM 8/1/08 7:47 AM 8/1/08 7:56 AM 8/1/08 7:58 AM 8/1/08 8:13 AM 8/1/08 8:33 AM 8/1/08 9:07 AM 8/1/08 10:28 AM 8/1/08 10:38 AM 8/1/08 10:44 AM 8/1/08 ...

How export all mailbox-enabled users from the GAL using Outlook?
Hello, We have a user which, for administrative reasons, needs to export all mailbox-enabled users in the GAL now and then. They just need all the names. Is there a straightforward way for them to do that? We don't want to give them any special permissions and want to avoid server scripting. Ideally, they should be able to export it to a CSV file or any text file. Thanks, - Alan. Alan wrote: > We have a user which, for administrative reasons, needs to export all > mailbox-enabled users in the GAL now and then. They just need all the > names. Is there a straightforward way fo...

If a worksheet name is = to test then a msgbox appears
I'm looking for a macro that will display a msgbox if a worksheet is = to test. For example, if the name of a sheet in a workbook is equal to test then display msgbox saying sheet already exists. Thanks Vick dim ws as worksheet set ws = nothing on error resume next set ws = worksheets("test") on error goto 0 if ws is nothing then msgbox "doesn't exist" else msgbox "already exists" end if Vick wrote: > > I'm looking for a macro that will display a msgbox if a worksheet is = to test. > > For example, if the name of a sheet in a w...

SQL 2008 running on a VM using all allocated memory
Hi, I've got a sql2008 server running on a VM. There's 9GB of physical RAM, which 7GB have been allocated to SQL Server. But when i look at task manager, i see that the SQL server is actually using all 7GB, which is pegging the memory usage of the overal box at above 90% used. We're mostly a sql2005 shop, and none of those servers are doing this. I have sql2005 running on VM's, someone actual servers as a named or default instance, and some even clustered. None of them have this problem. Is this a normal thing with sql2008 only? Any insight would be greatly appre...

Anyone use Promys CRM software integrated with GP Dynamics?
Our company is considering using Promys as a CRM to create quotes and sales orders for the sales team. I am concerned about the integration with GP and what the pitfalls may be. Is anyone here currently using Promys with GP Dynamics? ...

Loading Text File to TextBox using LoadFromFile
Hi All, I'm creating a form that allows the user to pick a txt file (dialog) and then display the path and contents on the form. The code has been cobbled together as I found the pieces that worked, so bear with. I got the file picker working and displaying the file name on the form, but the file contents won't display. I had a feeling the problem had to do with importing a namespace (see the error in the code when I tried "Imports System.IO") or with a missing reference. Using Access 2003. References: VB for Apps, MS Access 11 Obj Lib, OLE Auto, MS V...

Can't use openURL or access internet on some locations
I have previously raised a question where openUrl threw an exception in a specific office location. However the problem I have now is that the application just freeze when I call openURL. It seems as it is waiting for something. This only happens at one company so far the company does have a proxy but so do I at work and I have no problems. I have also tried the Microsoft TEAR sample and it behaves the exact same way, it says "Opening internet...Connection made" and then it just stops. If I run it on my computer it gets the webpage and everything finishes ok. I have tried to set t...

Multipule Worksheets
How do you set Excel 2002 to open all spreadsheets into one document instead of having multipule documents open up across your toolbar? ...

changing the application name
hi, i have developed a project and after completion of that project there is need to change that applicatin name. I will explained clearly i have created a document./view project , which named has "aaaa" afte completion the project, by running that application, there is a frame which has title "aaaa" Now there is need to change that title to "bbbbb" how can we do that? i am using VC++.net plz let me know how to do that by, koti "Koti" <koti@nannacomputers.com> wrote in message news:OqHUbvNQFHA.1236@TK2MSFTNGP14.phx.gbl... >...

Connectning Outlook to MSN mail account (server names)
I need help trying to connect my outlook to my free MSN mail account. Please help, I need the server names to figure this out or a step by step guide to set this up. I have Microsoft Outlook 2000 Hi Steve please read this here http://home.arcor.de/andreas.roeder-privat/English%20Help.htm#19 -- Ich habe nichts gemacht, gestern gings noch! Bitte in den Newsgroup antworten damit jeder etwas davon hat. Bravestar@Datenschutzministerium.de "Steve" <aefwolf@msn.com> schrieb im Newsbeitrag news:0c9d01c46e8c$73b6c090$a501280a@phx.gbl... > I need help trying to connect my outlook ...