| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Imagebox Property?
I posted the code earlier on a different topic:
Private Sub Image1_Click() FileToOpen = Application.GetOpenFilename( _ "All Files (*.jpg),*.jpg,(*.bmp),*.bmp") If FileToOpen < False Then Worksheets("Sheet1").OLEObjects("Image1").Object.P icture _ = LoadPicture(FileToOpen) End If End Sub If I want to remove the image, I have to change the image box Picture property to "none". Can anyone help with the code for setting properties? I think I'd need something like Worksheets("Sheet1").OLEObjects("Image1").Object.P icture _ = None Is it possible to assign this to a right-click event? Or, maybe I should add some code for a message box prompting to add or remove an image. I'm not sure what the simplest solution is. I'm so rusty on VB code - it's been years since I messed with it. Thanks folks! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Imagebox Property?
Try it this way...
Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = LoadPicture("") -- Rick (MVP - Excel) "mooresk257" wrote in message ... I posted the code earlier on a different topic: Private Sub Image1_Click() FileToOpen = Application.GetOpenFilename( _ "All Files (*.jpg),*.jpg,(*.bmp),*.bmp") If FileToOpen < False Then Worksheets("Sheet1").OLEObjects("Image1").Object.P icture _ = LoadPicture(FileToOpen) End If End Sub If I want to remove the image, I have to change the image box Picture property to "none". Can anyone help with the code for setting properties? I think I'd need something like Worksheets("Sheet1").OLEObjects("Image1").Object.P icture _ = None Is it possible to assign this to a right-click event? Or, maybe I should add some code for a message box prompting to add or remove an image. I'm not sure what the simplest solution is. I'm so rusty on VB code - it's been years since I messed with it. Thanks folks! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Imagebox Property?
OK, thanks - that works. Almost got this thing wrapped up - one more question
(I hope) on this. My code for the image box looks like this: 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.P icture _ = LoadPicture(FileToOpen) End If ElseIf NewImg = vbNo Then ' If Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = ("") Then ' End If DelImg = MsgBox("Remove Current Photo?", vbYesNo) If DelImg = vbYes Then Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = LoadPicture("") ElseIf DelImg = vbNo Then End If ElseIf NewImg = vbCancel Then End If End Sub Note the two line I have commented out - I want to skip having the second message box pop up if the image box is empty, but this line returns an object error. So, obviously I've got something wrong somewhere. I only get an object error if there is no image in the photo box. Suggestions? Also, anyone have a suggestion for a book on VBA? Something written so the casual user can understand? "Rick Rothstein" wrote: Try it this way... Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = LoadPicture("") -- Rick (MVP - Excel) "mooresk257" wrote in message ... I posted the code earlier on a different topic: Private Sub Image1_Click() FileToOpen = Application.GetOpenFilename( _ "All Files (*.jpg),*.jpg,(*.bmp),*.bmp") If FileToOpen < False Then Worksheets("Sheet1").OLEObjects("Image1").Object.P icture _ = LoadPicture(FileToOpen) End If End Sub If I want to remove the image, I have to change the image box Picture property to "none". Can anyone help with the code for setting properties? I think I'd need something like Worksheets("Sheet1").OLEObjects("Image1").Object.P icture _ = None Is it possible to assign this to a right-click event? Or, maybe I should add some code for a message box prompting to add or remove an image. I'm not sure what the simplest solution is. I'm so rusty on VB code - it's been years since I messed with it. Thanks folks! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Imagebox Property?
Try this If..Then statement...
If Worksheets("Sheet1").OLEObjects("Image1").Object.P icture Is Nothing Then -- Rick (MVP - Excel) "mooresk257" wrote in message ... OK, thanks - that works. Almost got this thing wrapped up - one more question (I hope) on this. My code for the image box looks like this: 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.P icture _ = LoadPicture(FileToOpen) End If ElseIf NewImg = vbNo Then ' If Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = ("") Then ' End If DelImg = MsgBox("Remove Current Photo?", vbYesNo) If DelImg = vbYes Then Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = LoadPicture("") ElseIf DelImg = vbNo Then End If ElseIf NewImg = vbCancel Then End If End Sub Note the two line I have commented out - I want to skip having the second message box pop up if the image box is empty, but this line returns an object error. So, obviously I've got something wrong somewhere. I only get an object error if there is no image in the photo box. Suggestions? Also, anyone have a suggestion for a book on VBA? Something written so the casual user can understand? "Rick Rothstein" wrote: Try it this way... Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = LoadPicture("") -- Rick (MVP - Excel) "mooresk257" wrote in message ... I posted the code earlier on a different topic: Private Sub Image1_Click() FileToOpen = Application.GetOpenFilename( _ "All Files (*.jpg),*.jpg,(*.bmp),*.bmp") If FileToOpen < False Then Worksheets("Sheet1").OLEObjects("Image1").Object.P icture _ = LoadPicture(FileToOpen) End If End Sub If I want to remove the image, I have to change the image box Picture property to "none". Can anyone help with the code for setting properties? I think I'd need something like Worksheets("Sheet1").OLEObjects("Image1").Object.P icture _ = None Is it possible to assign this to a right-click event? Or, maybe I should add some code for a message box prompting to add or remove an image. I'm not sure what the simplest solution is. I'm so rusty on VB code - it's been years since I messed with it. Thanks folks! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Imagebox Property?
I didn't get any errors, but it didn't change anything either. The property
for the photo is either (None) or (Bitmap), regardless whether hte image inserted in jpg or bmp. So "Object.Picture Is Nothing" does not seem to be returning a true or false value. I'd like it to skip the second msgbox asking to remove a photo when there isn't one there - and I'm stumped how to do this. + "Rick Rothstein" wrote: Try this If..Then statement... If Worksheets("Sheet1").OLEObjects("Image1").Object.P icture Is Nothing Then -- Rick (MVP - Excel) "mooresk257" wrote in message ... OK, thanks - that works. Almost got this thing wrapped up - one more question (I hope) on this. My code for the image box looks like this: 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.P icture _ = LoadPicture(FileToOpen) End If ElseIf NewImg = vbNo Then ' If Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = ("") Then ' End If DelImg = MsgBox("Remove Current Photo?", vbYesNo) If DelImg = vbYes Then Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = LoadPicture("") ElseIf DelImg = vbNo Then End If ElseIf NewImg = vbCancel Then End If End Sub Note the two line I have commented out - I want to skip having the second message box pop up if the image box is empty, but this line returns an object error. So, obviously I've got something wrong somewhere. I only get an object error if there is no image in the photo box. Suggestions? Also, anyone have a suggestion for a book on VBA? Something written so the casual user can understand? "Rick Rothstein" wrote: Try it this way... Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = LoadPicture("") -- Rick (MVP - Excel) "mooresk257" wrote in message ... I posted the code earlier on a different topic: Private Sub Image1_Click() FileToOpen = Application.GetOpenFilename( _ "All Files (*.jpg),*.jpg,(*.bmp),*.bmp") If FileToOpen < False Then Worksheets("Sheet1").OLEObjects("Image1").Object.P icture _ = LoadPicture(FileToOpen) End If End Sub If I want to remove the image, I have to change the image box Picture property to "none". Can anyone help with the code for setting properties? I think I'd need something like Worksheets("Sheet1").OLEObjects("Image1").Object.P icture _ = None Is it possible to assign this to a right-click event? Or, maybe I should add some code for a message box prompting to add or remove an image. I'm not sure what the simplest solution is. I'm so rusty on VB code - it's been years since I messed with it. Thanks folks! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Imagebox Property?
"Object.Picture Is Nothing" wasn't what I said to test; however, this...
Worksheets("Sheet1").OLEObjects("Image1").Object.P icture Is Nothing returned True (if there is no picture in the ImageBox) and False (if there is) for me when I tested it. -- Rick (MVP - Excel) "mooresk257" wrote in message ... I didn't get any errors, but it didn't change anything either. The property for the photo is either (None) or (Bitmap), regardless whether hte image inserted in jpg or bmp. So "Object.Picture Is Nothing" does not seem to be returning a true or false value. I'd like it to skip the second msgbox asking to remove a photo when there isn't one there - and I'm stumped how to do this. + "Rick Rothstein" wrote: Try this If..Then statement... If Worksheets("Sheet1").OLEObjects("Image1").Object.P icture Is Nothing Then -- Rick (MVP - Excel) "mooresk257" wrote in message ... OK, thanks - that works. Almost got this thing wrapped up - one more question (I hope) on this. My code for the image box looks like this: 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.P icture _ = LoadPicture(FileToOpen) End If ElseIf NewImg = vbNo Then ' If Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = ("") Then ' End If DelImg = MsgBox("Remove Current Photo?", vbYesNo) If DelImg = vbYes Then Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = LoadPicture("") ElseIf DelImg = vbNo Then End If ElseIf NewImg = vbCancel Then End If End Sub Note the two line I have commented out - I want to skip having the second message box pop up if the image box is empty, but this line returns an object error. So, obviously I've got something wrong somewhere. I only get an object error if there is no image in the photo box. Suggestions? Also, anyone have a suggestion for a book on VBA? Something written so the casual user can understand? "Rick Rothstein" wrote: Try it this way... Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = LoadPicture("") -- Rick (MVP - Excel) "mooresk257" wrote in message ... I posted the code earlier on a different topic: Private Sub Image1_Click() FileToOpen = Application.GetOpenFilename( _ "All Files (*.jpg),*.jpg,(*.bmp),*.bmp") If FileToOpen < False Then Worksheets("Sheet1").OLEObjects("Image1").Object.P icture _ = LoadPicture(FileToOpen) End If End Sub If I want to remove the image, I have to change the image box Picture property to "none". Can anyone help with the code for setting properties? I think I'd need something like Worksheets("Sheet1").OLEObjects("Image1").Object.P icture _ = None Is it possible to assign this to a right-click event? Or, maybe I should add some code for a message box prompting to add or remove an image. I'm not sure what the simplest solution is. I'm so rusty on VB code - it's been years since I messed with it. Thanks folks! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Imagebox Property?
When I put this in my code posted earlier:
If Worksheets("Sheet1").OLEObjects("Image1").Object.P icture Is Nothing Then End If The next statement: DelImg = MsgBox("Remove Current Photo?", vbYesNo) still executes, as it probably should. So adding this "If" statement doesn't skip over the following prompt to remove an image that isn't there. I tried a GoTo to try and skip over the second msgbox statment if the "Object.Picture Is Nothing" , but I get an error. I think I must be missing something obvious here. "Rick Rothstein" wrote: "Object.Picture Is Nothing" wasn't what I said to test; however, this... Worksheets("Sheet1").OLEObjects("Image1").Object.P icture Is Nothing returned True (if there is no picture in the ImageBox) and False (if there is) for me when I tested it. -- Rick (MVP - Excel) "mooresk257" wrote in message ... I didn't get any errors, but it didn't change anything either. The property for the photo is either (None) or (Bitmap), regardless whether hte image inserted in jpg or bmp. So "Object.Picture Is Nothing" does not seem to be returning a true or false value. I'd like it to skip the second msgbox asking to remove a photo when there isn't one there - and I'm stumped how to do this. + "Rick Rothstein" wrote: Try this If..Then statement... If Worksheets("Sheet1").OLEObjects("Image1").Object.P icture Is Nothing Then -- Rick (MVP - Excel) "mooresk257" wrote in message ... OK, thanks - that works. Almost got this thing wrapped up - one more question (I hope) on this. My code for the image box looks like this: 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.P icture _ = LoadPicture(FileToOpen) End If ElseIf NewImg = vbNo Then ' If Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = ("") Then ' End If DelImg = MsgBox("Remove Current Photo?", vbYesNo) If DelImg = vbYes Then Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = LoadPicture("") ElseIf DelImg = vbNo Then End If ElseIf NewImg = vbCancel Then End If End Sub Note the two line I have commented out - I want to skip having the second message box pop up if the image box is empty, but this line returns an object error. So, obviously I've got something wrong somewhere. I only get an object error if there is no image in the photo box. Suggestions? Also, anyone have a suggestion for a book on VBA? Something written so the casual user can understand? "Rick Rothstein" wrote: Try it this way... Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = LoadPicture("") -- Rick (MVP - Excel) "mooresk257" wrote in message ... I posted the code earlier on a different topic: Private Sub Image1_Click() FileToOpen = Application.GetOpenFilename( _ "All Files (*.jpg),*.jpg,(*.bmp),*.bmp") If FileToOpen < False Then Worksheets("Sheet1").OLEObjects("Image1").Object.P icture _ = LoadPicture(FileToOpen) End If End Sub If I want to remove the image, I have to change the image box Picture property to "none". Can anyone help with the code for setting properties? I think I'd need something like Worksheets("Sheet1").OLEObjects("Image1").Object.P icture _ = None Is it possible to assign this to a right-click event? Or, maybe I should add some code for a message box prompting to add or remove an image. I'm not sure what the simplest solution is. I'm so rusty on VB code - it's been years since I messed with it. Thanks folks! |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Imagebox Property?
You need to put your code between the If..Then statement and the End If
statement if you want it to be controlled by the logical expression in the If..Then statement. Try this... 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.P icture _ = LoadPicture(FileToOpen) End If ElseIf NewImg = vbNo Then If Worksheets("Sheet1").OLEObjects("Image1"). _ Object.Picture Is Nothing Then DelImg = MsgBox("Remove Current Photo?", vbYesNo) If DelImg = vbYes Then Worksheets("Sheet1").OLEObjects("Image1"). _ Object.Picture = LoadPicture("") End If End If End If End Sub NOTE: The above code is off-the-top-of-my-head... I didn't try it out (I simply restructured what you already had). -- Rick (MVP - Excel) "mooresk257" wrote in message ... When I put this in my code posted earlier: If Worksheets("Sheet1").OLEObjects("Image1").Object.P icture Is Nothing Then End If The next statement: DelImg = MsgBox("Remove Current Photo?", vbYesNo) still executes, as it probably should. So adding this "If" statement doesn't skip over the following prompt to remove an image that isn't there. I tried a GoTo to try and skip over the second msgbox statment if the "Object.Picture Is Nothing" , but I get an error. I think I must be missing something obvious here. "Rick Rothstein" wrote: "Object.Picture Is Nothing" wasn't what I said to test; however, this... Worksheets("Sheet1").OLEObjects("Image1").Object.P icture Is Nothing returned True (if there is no picture in the ImageBox) and False (if there is) for me when I tested it. -- Rick (MVP - Excel) "mooresk257" wrote in message ... I didn't get any errors, but it didn't change anything either. The property for the photo is either (None) or (Bitmap), regardless whether hte image inserted in jpg or bmp. So "Object.Picture Is Nothing" does not seem to be returning a true or false value. I'd like it to skip the second msgbox asking to remove a photo when there isn't one there - and I'm stumped how to do this. + "Rick Rothstein" wrote: Try this If..Then statement... If Worksheets("Sheet1").OLEObjects("Image1").Object.P icture Is Nothing Then -- Rick (MVP - Excel) "mooresk257" wrote in message ... OK, thanks - that works. Almost got this thing wrapped up - one more question (I hope) on this. My code for the image box looks like this: 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.P icture _ = LoadPicture(FileToOpen) End If ElseIf NewImg = vbNo Then ' If Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = ("") Then ' End If DelImg = MsgBox("Remove Current Photo?", vbYesNo) If DelImg = vbYes Then Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = LoadPicture("") ElseIf DelImg = vbNo Then End If ElseIf NewImg = vbCancel Then End If End Sub Note the two line I have commented out - I want to skip having the second message box pop up if the image box is empty, but this line returns an object error. So, obviously I've got something wrong somewhere. I only get an object error if there is no image in the photo box. Suggestions? Also, anyone have a suggestion for a book on VBA? Something written so the casual user can understand? "Rick Rothstein" wrote: Try it this way... Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = LoadPicture("") -- Rick (MVP - Excel) "mooresk257" wrote in message ... I posted the code earlier on a different topic: Private Sub Image1_Click() FileToOpen = Application.GetOpenFilename( _ "All Files (*.jpg),*.jpg,(*.bmp),*.bmp") If FileToOpen < False Then Worksheets("Sheet1").OLEObjects("Image1").Object.P icture _ = LoadPicture(FileToOpen) End If End Sub If I want to remove the image, I have to change the image box Picture property to "none". Can anyone help with the code for setting properties? I think I'd need something like Worksheets("Sheet1").OLEObjects("Image1").Object.P icture _ = None Is it possible to assign this to a right-click event? Or, maybe I should add some code for a message box prompting to add or remove an image. I'm not sure what the simplest solution is. I'm so rusty on VB code - it's been years since I messed with it. Thanks folks! |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Imagebox Property?
OK, I got it to work - this is the code I ended up with:
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.P icture _ = LoadPicture(FileToOpen) 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.P icture = LoadPicture("") ElseIf DelImg = vbNo Then End If ElseIf NewImg = vbCancel Then End If Skip: End Sub Thanks for your help Rick! "Rick Rothstein" wrote: You need to put your code between the If..Then statement and the End If statement if you want it to be controlled by the logical expression in the If..Then statement. Try this... 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.P icture _ = LoadPicture(FileToOpen) End If ElseIf NewImg = vbNo Then If Worksheets("Sheet1").OLEObjects("Image1"). _ Object.Picture Is Nothing Then DelImg = MsgBox("Remove Current Photo?", vbYesNo) If DelImg = vbYes Then Worksheets("Sheet1").OLEObjects("Image1"). _ Object.Picture = LoadPicture("") End If End If End If End Sub NOTE: The above code is off-the-top-of-my-head... I didn't try it out (I simply restructured what you already had). -- Rick (MVP - Excel) "mooresk257" wrote in message ... When I put this in my code posted earlier: If Worksheets("Sheet1").OLEObjects("Image1").Object.P icture Is Nothing Then End If The next statement: DelImg = MsgBox("Remove Current Photo?", vbYesNo) still executes, as it probably should. So adding this "If" statement doesn't skip over the following prompt to remove an image that isn't there. I tried a GoTo to try and skip over the second msgbox statment if the "Object.Picture Is Nothing" , but I get an error. I think I must be missing something obvious here. "Rick Rothstein" wrote: "Object.Picture Is Nothing" wasn't what I said to test; however, this... Worksheets("Sheet1").OLEObjects("Image1").Object.P icture Is Nothing returned True (if there is no picture in the ImageBox) and False (if there is) for me when I tested it. -- Rick (MVP - Excel) "mooresk257" wrote in message ... I didn't get any errors, but it didn't change anything either. The property for the photo is either (None) or (Bitmap), regardless whether hte image inserted in jpg or bmp. So "Object.Picture Is Nothing" does not seem to be returning a true or false value. I'd like it to skip the second msgbox asking to remove a photo when there isn't one there - and I'm stumped how to do this. + "Rick Rothstein" wrote: Try this If..Then statement... If Worksheets("Sheet1").OLEObjects("Image1").Object.P icture Is Nothing Then -- Rick (MVP - Excel) "mooresk257" wrote in message ... OK, thanks - that works. Almost got this thing wrapped up - one more question (I hope) on this. My code for the image box looks like this: 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.P icture _ = LoadPicture(FileToOpen) End If ElseIf NewImg = vbNo Then ' If Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = ("") Then ' End If DelImg = MsgBox("Remove Current Photo?", vbYesNo) If DelImg = vbYes Then Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = LoadPicture("") ElseIf DelImg = vbNo Then End If ElseIf NewImg = vbCancel Then End If End Sub Note the two line I have commented out - I want to skip having the second message box pop up if the image box is empty, but this line returns an object error. So, obviously I've got something wrong somewhere. I only get an object error if there is no image in the photo box. Suggestions? Also, anyone have a suggestion for a book on VBA? Something written so the casual user can understand? "Rick Rothstein" wrote: Try it this way... Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = LoadPicture("") -- Rick (MVP - Excel) "mooresk257" wrote in message ... I posted the code earlier on a different topic: Private Sub Image1_Click() FileToOpen = Application.GetOpenFilename( _ "All Files (*.jpg),*.jpg,(*.bmp),*.bmp") If FileToOpen < False Then Worksheets("Sheet1").OLEObjects("Image1").Object.P icture _ = LoadPicture(FileToOpen) End If End Sub If I want to remove the image, I have to change the image box Picture property to "none". Can anyone help with the code for setting properties? I think I'd need something like Worksheets("Sheet1").OLEObjects("Image1").Object.P icture _ = None Is it possible to assign this to a right-click event? Or, maybe I should add some code for a message box prompting to add or remove an image. I'm not sure what the simplest solution is. I'm so rusty on VB code - it's been years since I messed with it. Thanks folks! |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Imagebox Property?
You are quite welcome. I would suggest you learn to not use GoTo statements
if you can help it as they tend to make code harder to read. Here is your code revised to remove the GoTo (all I did is negate the logical test in the If..Then statement, that is what the Not operator is doing, and move the code into the If..Then..EndIf block). I also remove to ElseIf statements because you had no code to execute for them. 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.P icture _ = LoadPicture(FileToOpen) End If ElseIf NewImg = vbNo Then If Not Worksheets("Sheet1").OLEObjects("Image1"). _ Object.Picture Is Nothing Then DelImg = MsgBox("Remove Current Photo?", vbYesNo) If DelImg = vbYes Then Worksheets("Sheet1").OLEObjects("Image1").Object.P icture _ = LoadPicture("") End If End If End If End Sub -- Rick (MVP - Excel) "mooresk257" wrote in message ... OK, I got it to work - this is the code I ended up with: 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.P icture _ = LoadPicture(FileToOpen) 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.P icture = LoadPicture("") ElseIf DelImg = vbNo Then End If ElseIf NewImg = vbCancel Then End If Skip: End Sub Thanks for your help Rick! "Rick Rothstein" wrote: You need to put your code between the If..Then statement and the End If statement if you want it to be controlled by the logical expression in the If..Then statement. Try this... 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.P icture _ = LoadPicture(FileToOpen) End If ElseIf NewImg = vbNo Then If Worksheets("Sheet1").OLEObjects("Image1"). _ Object.Picture Is Nothing Then DelImg = MsgBox("Remove Current Photo?", vbYesNo) If DelImg = vbYes Then Worksheets("Sheet1").OLEObjects("Image1"). _ Object.Picture = LoadPicture("") End If End If End If End Sub NOTE: The above code is off-the-top-of-my-head... I didn't try it out (I simply restructured what you already had). -- Rick (MVP - Excel) "mooresk257" wrote in message ... When I put this in my code posted earlier: If Worksheets("Sheet1").OLEObjects("Image1").Object.P icture Is Nothing Then End If The next statement: DelImg = MsgBox("Remove Current Photo?", vbYesNo) still executes, as it probably should. So adding this "If" statement doesn't skip over the following prompt to remove an image that isn't there. I tried a GoTo to try and skip over the second msgbox statment if the "Object.Picture Is Nothing" , but I get an error. I think I must be missing something obvious here. "Rick Rothstein" wrote: "Object.Picture Is Nothing" wasn't what I said to test; however, this... Worksheets("Sheet1").OLEObjects("Image1").Object.P icture Is Nothing returned True (if there is no picture in the ImageBox) and False (if there is) for me when I tested it. -- Rick (MVP - Excel) "mooresk257" wrote in message ... I didn't get any errors, but it didn't change anything either. The property for the photo is either (None) or (Bitmap), regardless whether hte image inserted in jpg or bmp. So "Object.Picture Is Nothing" does not seem to be returning a true or false value. I'd like it to skip the second msgbox asking to remove a photo when there isn't one there - and I'm stumped how to do this. + "Rick Rothstein" wrote: Try this If..Then statement... If Worksheets("Sheet1").OLEObjects("Image1").Object.P icture Is Nothing Then -- Rick (MVP - Excel) "mooresk257" wrote in message ... OK, thanks - that works. Almost got this thing wrapped up - one more question (I hope) on this. My code for the image box looks like this: 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.P icture _ = LoadPicture(FileToOpen) End If ElseIf NewImg = vbNo Then ' If Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = ("") Then ' End If DelImg = MsgBox("Remove Current Photo?", vbYesNo) If DelImg = vbYes Then Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = LoadPicture("") ElseIf DelImg = vbNo Then End If ElseIf NewImg = vbCancel Then End If End Sub Note the two line I have commented out - I want to skip having the second message box pop up if the image box is empty, but this line returns an object error. So, obviously I've got something wrong somewhere. I only get an object error if there is no image in the photo box. Suggestions? Also, anyone have a suggestion for a book on VBA? Something written so the casual user can understand? "Rick Rothstein" wrote: Try it this way... Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = LoadPicture("") -- Rick (MVP - Excel) "mooresk257" wrote in message ... I posted the code earlier on a different topic: Private Sub Image1_Click() FileToOpen = Application.GetOpenFilename( _ "All Files (*.jpg),*.jpg,(*.bmp),*.bmp") If FileToOpen < False Then Worksheets("Sheet1").OLEObjects("Image1").Object.P icture _ = LoadPicture(FileToOpen) End If End Sub If I want to remove the image, I have to change the image box Picture property to "none". Can anyone help with the code for setting properties? I think I'd need something like Worksheets("Sheet1").OLEObjects("Image1").Object.P icture _ = None Is it possible to assign this to a right-click event? Or, maybe I should add some code for a message box prompting to add or remove an image. I'm not sure what the simplest solution is. I'm so rusty on VB code - it's been years since I messed with it. Thanks folks! |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Imagebox Property?
I get a "subscript out of range" error when I click "No" on the first message
box with this code. "Rick Rothstein" wrote: You are quite welcome. I would suggest you learn to not use GoTo statements if you can help it as they tend to make code harder to read. Here is your code revised to remove the GoTo (all I did is negate the logical test in the If..Then statement, that is what the Not operator is doing, and move the code into the If..Then..EndIf block). I also remove to ElseIf statements because you had no code to execute for them. 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.P icture _ = LoadPicture(FileToOpen) End If ElseIf NewImg = vbNo Then If Not Worksheets("Sheet1").OLEObjects("Image1"). _ Object.Picture Is Nothing Then DelImg = MsgBox("Remove Current Photo?", vbYesNo) If DelImg = vbYes Then Worksheets("Sheet1").OLEObjects("Image1").Object.P icture _ = LoadPicture("") End If End If End If End Sub -- Rick (MVP - Excel) "mooresk257" wrote in message ... OK, I got it to work - this is the code I ended up with: 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.P icture _ = LoadPicture(FileToOpen) 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.P icture = LoadPicture("") ElseIf DelImg = vbNo Then End If ElseIf NewImg = vbCancel Then End If Skip: End Sub Thanks for your help Rick! "Rick Rothstein" wrote: You need to put your code between the If..Then statement and the End If statement if you want it to be controlled by the logical expression in the If..Then statement. Try this... 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.P icture _ = LoadPicture(FileToOpen) End If ElseIf NewImg = vbNo Then If Worksheets("Sheet1").OLEObjects("Image1"). _ Object.Picture Is Nothing Then DelImg = MsgBox("Remove Current Photo?", vbYesNo) If DelImg = vbYes Then Worksheets("Sheet1").OLEObjects("Image1"). _ Object.Picture = LoadPicture("") End If End If End If End Sub NOTE: The above code is off-the-top-of-my-head... I didn't try it out (I simply restructured what you already had). -- Rick (MVP - Excel) "mooresk257" wrote in message ... When I put this in my code posted earlier: If Worksheets("Sheet1").OLEObjects("Image1").Object.P icture Is Nothing Then End If The next statement: DelImg = MsgBox("Remove Current Photo?", vbYesNo) still executes, as it probably should. So adding this "If" statement doesn't skip over the following prompt to remove an image that isn't there. I tried a GoTo to try and skip over the second msgbox statment if the "Object.Picture Is Nothing" , but I get an error. I think I must be missing something obvious here. "Rick Rothstein" wrote: "Object.Picture Is Nothing" wasn't what I said to test; however, this... Worksheets("Sheet1").OLEObjects("Image1").Object.P icture Is Nothing returned True (if there is no picture in the ImageBox) and False (if there is) for me when I tested it. -- Rick (MVP - Excel) "mooresk257" wrote in message ... I didn't get any errors, but it didn't change anything either. The property for the photo is either (None) or (Bitmap), regardless whether hte image inserted in jpg or bmp. So "Object.Picture Is Nothing" does not seem to be returning a true or false value. I'd like it to skip the second msgbox asking to remove a photo when there isn't one there - and I'm stumped how to do this. + "Rick Rothstein" wrote: Try this If..Then statement... If Worksheets("Sheet1").OLEObjects("Image1").Object.P icture Is Nothing Then -- Rick (MVP - Excel) "mooresk257" wrote in message ... OK, thanks - that works. Almost got this thing wrapped up - one more question (I hope) on this. My code for the image box looks like this: 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.P icture _ = LoadPicture(FileToOpen) End If ElseIf NewImg = vbNo Then ' If Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = ("") Then ' End If DelImg = MsgBox("Remove Current Photo?", vbYesNo) If DelImg = vbYes Then Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = LoadPicture("") ElseIf DelImg = vbNo Then End If ElseIf NewImg = vbCancel Then End If End Sub Note the two line I have commented out - I want to skip having the second message box pop up if the image box is empty, but this line returns an object error. So, obviously I've got something wrong somewhere. I only get an object error if there is no image in the photo box. Suggestions? Also, anyone have a suggestion for a book on VBA? Something written so the casual user can understand? "Rick Rothstein" wrote: Try it this way... Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = LoadPicture("") -- Rick (MVP - Excel) "mooresk257" wrote in message ... I posted the code earlier on a different topic: Private Sub Image1_Click() FileToOpen = Application.GetOpenFilename( _ "All Files (*.jpg),*.jpg,(*.bmp),*.bmp") If FileToOpen < False Then Worksheets("Sheet1").OLEObjects("Image1").Object.P icture _ = LoadPicture(FileToOpen) End If End Sub If I want to remove the image, I have to change the image box Picture property to "none". Can anyone help with the code for setting properties? I think I'd need something like Worksheets("Sheet1").OLEObjects("Image1").Object.P icture _ = None Is it possible to assign this to a right-click event? Or, maybe I should add some code for a message box prompting to add or remove an image. I'm not sure what the simplest solution is. I'm so rusty on VB code - it's been years since I messed with it. Thanks folks! |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Imagebox Property?
That probably means your ImageBox is not on Sheet1. I used Sheet1 for the
sheet name because you did so in your first posting. Change the three occurrences of Sheet1 to the actual name of your worksheet. I guess another possibility is that your ImageBox is not named Image1. By the way, I just tested the code and it works fine except for this line... If FileToOpen < False Then Two things... First, you should Dim FileToOpen as a String. Second, you would then need to test it like this... If Len(FileToOpen) 0 Then -- Rick (MVP - Excel) "mooresk257" wrote in message ... I get a "subscript out of range" error when I click "No" on the first message box with this code. "Rick Rothstein" wrote: You are quite welcome. I would suggest you learn to not use GoTo statements if you can help it as they tend to make code harder to read. Here is your code revised to remove the GoTo (all I did is negate the logical test in the If..Then statement, that is what the Not operator is doing, and move the code into the If..Then..EndIf block). I also remove to ElseIf statements because you had no code to execute for them. 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.P icture _ = LoadPicture(FileToOpen) End If ElseIf NewImg = vbNo Then If Not Worksheets("Sheet1").OLEObjects("Image1"). _ Object.Picture Is Nothing Then DelImg = MsgBox("Remove Current Photo?", vbYesNo) If DelImg = vbYes Then Worksheets("Sheet1").OLEObjects("Image1").Object.P icture _ = LoadPicture("") End If End If End If End Sub -- Rick (MVP - Excel) "mooresk257" wrote in message ... OK, I got it to work - this is the code I ended up with: 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.P icture _ = LoadPicture(FileToOpen) 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.P icture = LoadPicture("") ElseIf DelImg = vbNo Then End If ElseIf NewImg = vbCancel Then End If Skip: End Sub Thanks for your help Rick! "Rick Rothstein" wrote: You need to put your code between the If..Then statement and the End If statement if you want it to be controlled by the logical expression in the If..Then statement. Try this... 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.P icture _ = LoadPicture(FileToOpen) End If ElseIf NewImg = vbNo Then If Worksheets("Sheet1").OLEObjects("Image1"). _ Object.Picture Is Nothing Then DelImg = MsgBox("Remove Current Photo?", vbYesNo) If DelImg = vbYes Then Worksheets("Sheet1").OLEObjects("Image1"). _ Object.Picture = LoadPicture("") End If End If End If End Sub NOTE: The above code is off-the-top-of-my-head... I didn't try it out (I simply restructured what you already had). -- Rick (MVP - Excel) "mooresk257" wrote in message ... When I put this in my code posted earlier: If Worksheets("Sheet1").OLEObjects("Image1").Object.P icture Is Nothing Then End If The next statement: DelImg = MsgBox("Remove Current Photo?", vbYesNo) still executes, as it probably should. So adding this "If" statement doesn't skip over the following prompt to remove an image that isn't there. I tried a GoTo to try and skip over the second msgbox statment if the "Object.Picture Is Nothing" , but I get an error. I think I must be missing something obvious here. "Rick Rothstein" wrote: "Object.Picture Is Nothing" wasn't what I said to test; however, this... Worksheets("Sheet1").OLEObjects("Image1").Object.P icture Is Nothing returned True (if there is no picture in the ImageBox) and False (if there is) for me when I tested it. -- Rick (MVP - Excel) "mooresk257" wrote in message ... I didn't get any errors, but it didn't change anything either. The property for the photo is either (None) or (Bitmap), regardless whether hte image inserted in jpg or bmp. So "Object.Picture Is Nothing" does not seem to be returning a true or false value. I'd like it to skip the second msgbox asking to remove a photo when there isn't one there - and I'm stumped how to do this. + "Rick Rothstein" wrote: Try this If..Then statement... If Worksheets("Sheet1").OLEObjects("Image1").Object.P icture Is Nothing Then -- Rick (MVP - Excel) "mooresk257" wrote in message ... OK, thanks - that works. Almost got this thing wrapped up - one more question (I hope) on this. My code for the image box looks like this: 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.P icture _ = LoadPicture(FileToOpen) End If ElseIf NewImg = vbNo Then ' If Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = ("") Then ' End If DelImg = MsgBox("Remove Current Photo?", vbYesNo) If DelImg = vbYes Then Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = LoadPicture("") ElseIf DelImg = vbNo Then End If ElseIf NewImg = vbCancel Then End If End Sub Note the two line I have commented out - I want to skip having the second message box pop up if the image box is empty, but this line returns an object error. So, obviously I've got something wrong somewhere. I only get an object error if there is no image in the photo box. Suggestions? Also, anyone have a suggestion for a book on VBA? Something written so the casual user can understand? "Rick Rothstein" wrote: Try it this way... Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = LoadPicture("") -- Rick (MVP - Excel) "mooresk257" wrote in message ... I posted the code earlier on a different topic: Private Sub Image1_Click() FileToOpen = Application.GetOpenFilename( _ "All Files (*.jpg),*.jpg,(*.bmp),*.bmp") If FileToOpen < False Then Worksheets("Sheet1").OLEObjects("Image1").Object.P icture _ = LoadPicture(FileToOpen) End If End Sub If I want to remove the image, I have to change the image box Picture property to "none". Can anyone help with the code for setting properties? I think I'd need something like Worksheets("Sheet1").OLEObjects("Image1").Object.P icture _ = None Is it possible to assign this to a right-click event? Or, maybe I should add some code for a message box prompting to add or remove an image. I'm not sure what the simplest solution is. I'm so rusty on VB code - it's been years since I messed with it. Thanks folks! |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Imagebox Property?
OK thanks. I did change the sheet name, and updated it in the code, and still
got the error - I must have had something else wrong. I'll try it out & let you know how it works. "Rick Rothstein" wrote: That probably means your ImageBox is not on Sheet1. I used Sheet1 for the sheet name because you did so in your first posting. Change the three occurrences of Sheet1 to the actual name of your worksheet. I guess another possibility is that your ImageBox is not named Image1. By the way, I just tested the code and it works fine except for this line... If FileToOpen < False Then Two things... First, you should Dim FileToOpen as a String. Second, you would then need to test it like this... If Len(FileToOpen) 0 Then -- Rick (MVP - Excel) "mooresk257" wrote in message ... I get a "subscript out of range" error when I click "No" on the first message box with this code. "Rick Rothstein" wrote: You are quite welcome. I would suggest you learn to not use GoTo statements if you can help it as they tend to make code harder to read. Here is your code revised to remove the GoTo (all I did is negate the logical test in the If..Then statement, that is what the Not operator is doing, and move the code into the If..Then..EndIf block). I also remove to ElseIf statements because you had no code to execute for them. 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.P icture _ = LoadPicture(FileToOpen) End If ElseIf NewImg = vbNo Then If Not Worksheets("Sheet1").OLEObjects("Image1"). _ Object.Picture Is Nothing Then DelImg = MsgBox("Remove Current Photo?", vbYesNo) If DelImg = vbYes Then Worksheets("Sheet1").OLEObjects("Image1").Object.P icture _ = LoadPicture("") End If End If End If End Sub -- Rick (MVP - Excel) "mooresk257" wrote in message ... OK, I got it to work - this is the code I ended up with: 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.P icture _ = LoadPicture(FileToOpen) 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.P icture = LoadPicture("") ElseIf DelImg = vbNo Then End If ElseIf NewImg = vbCancel Then End If Skip: End Sub Thanks for your help Rick! "Rick Rothstein" wrote: You need to put your code between the If..Then statement and the End If statement if you want it to be controlled by the logical expression in the If..Then statement. Try this... 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.P icture _ = LoadPicture(FileToOpen) End If ElseIf NewImg = vbNo Then If Worksheets("Sheet1").OLEObjects("Image1"). _ Object.Picture Is Nothing Then DelImg = MsgBox("Remove Current Photo?", vbYesNo) If DelImg = vbYes Then Worksheets("Sheet1").OLEObjects("Image1"). _ Object.Picture = LoadPicture("") End If End If End If End Sub NOTE: The above code is off-the-top-of-my-head... I didn't try it out (I simply restructured what you already had). -- Rick (MVP - Excel) "mooresk257" wrote in message ... When I put this in my code posted earlier: If Worksheets("Sheet1").OLEObjects("Image1").Object.P icture Is Nothing Then End If The next statement: DelImg = MsgBox("Remove Current Photo?", vbYesNo) still executes, as it probably should. So adding this "If" statement doesn't skip over the following prompt to remove an image that isn't there. I tried a GoTo to try and skip over the second msgbox statment if the "Object.Picture Is Nothing" , but I get an error. I think I must be missing something obvious here. "Rick Rothstein" wrote: "Object.Picture Is Nothing" wasn't what I said to test; however, this... Worksheets("Sheet1").OLEObjects("Image1").Object.P icture Is Nothing returned True (if there is no picture in the ImageBox) and False (if there is) for me when I tested it. -- Rick (MVP - Excel) "mooresk257" wrote in message ... I didn't get any errors, but it didn't change anything either. The property for the photo is either (None) or (Bitmap), regardless whether hte image inserted in jpg or bmp. So "Object.Picture Is Nothing" does not seem to be returning a true or false value. I'd like it to skip the second msgbox asking to remove a photo when there isn't one there - and I'm stumped how to do this. + "Rick Rothstein" wrote: Try this If..Then statement... If Worksheets("Sheet1").OLEObjects("Image1").Object.P icture Is Nothing Then -- Rick (MVP - Excel) "mooresk257" wrote in message ... OK, thanks - that works. Almost got this thing wrapped up - one more question (I hope) on this. My code for the image box looks like this: 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.P icture _ = LoadPicture(FileToOpen) End If ElseIf NewImg = vbNo Then ' If Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = ("") Then ' End If DelImg = MsgBox("Remove Current Photo?", vbYesNo) If DelImg = vbYes Then Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = LoadPicture("") ElseIf DelImg = vbNo Then End If ElseIf NewImg = vbCancel Then End If End Sub Note the two line I have commented out - I want to skip having the second message box pop up if the image box is empty, but this line returns an object error. So, obviously I've got something wrong somewhere. I only get an object error if there is no image in the photo box. Suggestions? Also, anyone have a suggestion for a book on VBA? Something written so the casual user can understand? "Rick Rothstein" wrote: Try it this way... Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = LoadPicture("") -- Rick (MVP - Excel) "mooresk257" wrote in message ... |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Imagebox Property?
OK, that worked.
However, when I make the other changes (Dim and Len statements) it works unless I cancel in the file open dialog - then I get a "file not found" error. "mooresk257" wrote: OK thanks. I did change the sheet name, and updated it in the code, and still got the error - I must have had something else wrong. I'll try it out & let you know how it works. "Rick Rothstein" wrote: That probably means your ImageBox is not on Sheet1. I used Sheet1 for the sheet name because you did so in your first posting. Change the three occurrences of Sheet1 to the actual name of your worksheet. I guess another possibility is that your ImageBox is not named Image1. By the way, I just tested the code and it works fine except for this line... If FileToOpen < False Then Two things... First, you should Dim FileToOpen as a String. Second, you would then need to test it like this... If Len(FileToOpen) 0 Then -- Rick (MVP - Excel) "mooresk257" wrote in message ... I get a "subscript out of range" error when I click "No" on the first message box with this code. "Rick Rothstein" wrote: You are quite welcome. I would suggest you learn to not use GoTo statements if you can help it as they tend to make code harder to read. Here is your code revised to remove the GoTo (all I did is negate the logical test in the If..Then statement, that is what the Not operator is doing, and move the code into the If..Then..EndIf block). I also remove to ElseIf statements because you had no code to execute for them. 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.P icture _ = LoadPicture(FileToOpen) End If ElseIf NewImg = vbNo Then If Not Worksheets("Sheet1").OLEObjects("Image1"). _ Object.Picture Is Nothing Then DelImg = MsgBox("Remove Current Photo?", vbYesNo) If DelImg = vbYes Then Worksheets("Sheet1").OLEObjects("Image1").Object.P icture _ = LoadPicture("") End If End If End If End Sub -- Rick (MVP - Excel) "mooresk257" wrote in message ... OK, I got it to work - this is the code I ended up with: 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.P icture _ = LoadPicture(FileToOpen) 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.P icture = LoadPicture("") ElseIf DelImg = vbNo Then End If ElseIf NewImg = vbCancel Then End If Skip: End Sub Thanks for your help Rick! "Rick Rothstein" wrote: You need to put your code between the If..Then statement and the End If statement if you want it to be controlled by the logical expression in the If..Then statement. Try this... 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.P icture _ = LoadPicture(FileToOpen) End If ElseIf NewImg = vbNo Then If Worksheets("Sheet1").OLEObjects("Image1"). _ Object.Picture Is Nothing Then DelImg = MsgBox("Remove Current Photo?", vbYesNo) If DelImg = vbYes Then Worksheets("Sheet1").OLEObjects("Image1"). _ Object.Picture = LoadPicture("") End If End If End If End Sub NOTE: The above code is off-the-top-of-my-head... I didn't try it out (I simply restructured what you already had). -- Rick (MVP - Excel) "mooresk257" wrote in message ... When I put this in my code posted earlier: If Worksheets("Sheet1").OLEObjects("Image1").Object.P icture Is Nothing Then End If The next statement: DelImg = MsgBox("Remove Current Photo?", vbYesNo) still executes, as it probably should. So adding this "If" statement doesn't skip over the following prompt to remove an image that isn't there. I tried a GoTo to try and skip over the second msgbox statment if the "Object.Picture Is Nothing" , but I get an error. I think I must be missing something obvious here. "Rick Rothstein" wrote: "Object.Picture Is Nothing" wasn't what I said to test; however, this... Worksheets("Sheet1").OLEObjects("Image1").Object.P icture Is Nothing returned True (if there is no picture in the ImageBox) and False (if there is) for me when I tested it. -- Rick (MVP - Excel) "mooresk257" wrote in message ... I didn't get any errors, but it didn't change anything either. The property for the photo is either (None) or (Bitmap), regardless whether hte image inserted in jpg or bmp. So "Object.Picture Is Nothing" does not seem to be returning a true or false value. I'd like it to skip the second msgbox asking to remove a photo when there isn't one there - and I'm stumped how to do this. + "Rick Rothstein" wrote: Try this If..Then statement... If Worksheets("Sheet1").OLEObjects("Image1").Object.P icture Is Nothing Then -- Rick (MVP - Excel) "mooresk257" wrote in message ... OK, thanks - that works. Almost got this thing wrapped up - one more question (I hope) on this. My code for the image box looks like this: 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.P icture _ = LoadPicture(FileToOpen) End If ElseIf NewImg = vbNo Then ' If Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = ("") Then ' End If DelImg = MsgBox("Remove Current Photo?", vbYesNo) If DelImg = vbYes Then Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = LoadPicture("") ElseIf DelImg = vbNo Then End If ElseIf NewImg = vbCancel Then End If End Sub Note the two line I have commented out - I want to skip having the second message box pop up if the image box is empty, but this line returns an object error. So, obviously I've got something wrong somewhere. I only get an object error if there is no image in the photo box. Suggestions? Also, anyone have a suggestion for a book on VBA? Something written so the casual user can understand? "Rick Rothstein" wrote: Try it this way... Worksheets("Sheet1").OLEObjects("Image1").Object.P icture = LoadPicture("") |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| 'On change' property | Excel Programming | |||
| Change insidewidth property | Charts and Charting in Excel | |||
| Change property of comments only | Excel Programming | |||
| Combobox change property | Excel Programming | |||
| Problem using imagebox with wmf files | Excel Programming | |||
Linear Mode
