Code stopped adding hyperlink once again

[ipbr21054]

Would somebody please be willing to take a look at my code if i upload a file.

I thought i fixed the issue as its been working fine for a few days without fail but just now its stopped doing what its supposed to do.
Same issue as before without me even touching it.

I await a reply.
Many Thanks

 

[Joe4]

If you already have a thread on this topic, you should reply back to the original one, and not start a new one.
The people who helped you with the solution are probably the best ones to help you debug any issues you have with it.

 

[ipbr21054]

The original post was along the same lines but I sorted it myself or thought I did.
So I started a new post.

 

[ipbr21054]

As a test in the morning & to save me time I’m asking this here now.

At the end of my code I will have shown a yes no msg box.

No will exit sub but yes & this is where I need some help please, select the cell in column B of last row with values.

So if last row is 1000 & Mr smith is in B1000 the user would select yes to the msg box & cell B1000 would then be selected / active.

I can take it from there I think.
This posts issue has happened twice now & thinking of moving away from it if it’s going to be troublesome.
Hence this test.

Thanks.

 

[ipbr21054]

Code shown below.
Am i correct in thing the below is correct.

I see a Msgbox asking if there is a photo to hyperlink.
If NO then stop but if YES then the code that follows will continue to hyperlink the customers name to the photo in the given path.
Should you see an error as to why its failing please advise Thanks

       answer = MsgBox("IS THERE A PHOTO TO HYPERLINK ?", vbYesNo + vbInformation, "HYPERLINK PHOTO MESSAGE")
        If answer = vbNo Then
        Exit Sub
        Else

        If OptionButtonNo.Value = True Then
        Exit Sub
        End If
        
        If OptionButtonYes.Value = True Then

err:
        Const FILE_PATH As String = "C:\Users\Ian\Desktop\REMOTES ETC\DR\EBAY CUSTOMERS PHOTOS\"
        If ActiveCell.Column = Columns("B").Column Then
 
        If Len(Dir(FILE_PATH & ActiveCell.Value & ".jpg")) Then
          ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & ActiveCell.Value & ".jpg"
        MsgBox "CUSTOMER PHOTO HYPERLINK WAS SUCCESSFUL.", vbInformation, "SUCCESSFUL HYPERLINK MESSAGE"

        End If
        Else
            MsgBox "PLEASE SELECT A CUSTOMER FIRST TO HYPERLINK THE PHOTO.", vbCritical, "HYPERLINK ERROR MESSAGE"
        Exit Sub
        End If
        
        If Dir(FILE_PATH & ActiveCell.Value & ".jpg") = "" Then
        
        If MsgBox("THERE IS NO PHOTO TO HYPERLINK FOR THIS CUSTOMER" & vbCrLf & vbCrLf & _
            "WOULD YOU LIKE TO OPEN THE PHOTO FOLDER ?", vbYesNo + vbInformation, "HYPERLINK MISSING PHOTO MESSAGE.") = vbYes Then
        
            CreateObject("Shell.Application").Open ("C:\Users\Ian\Desktop\REMOTES ETC\DR\EBAY CUSTOMERS PHOTOS\")
            answer = MsgBox("CONTINUE TO NOW HYPERLINK THE CUSTOMER WITH PHOTO ?", vbYesNo, "HYPERLINK PHOTO MESSAGE")
        If answer = vbNo Then
        Exit Sub
        Else
                                            
        GoTo err
        End If
        End If
        End If
        End If
        End If
        
        End Sub

 

[Joe4]

Have you tried putting a breakpoint in your code and stepping through it line-by-line using the F8 key?
Then you can see the EXACT path this code is taking. That usually will expose where your issue is (where things are not working the way you expect).

 

[ipbr21054]

One of my pet hates & cant realy follow it

 

[ipbr21054]

If you could advise on post #4 i think i will just go that way as time isnt on my side & need to press on.
Couple hours this morning & ive got nowhere

 

[Joe4]

One of my pet hates & cant realy follow it

I am not really sure what you mean? But as a user who has been here for almost 15 years and has over 5000 posts and does a lot of work in VBA, learning the various ways to debug your code is essential. I find this is one of the most effective ways of debugging code when it does not seem to be doing what you expect. I usually just add a break point to the beginning of my procedure (it is very easy to do, just click on the gray column to the left of the line of code where you want to add the breakpoint, and it will show a red dot, as shown below):

Then, when you run your code, it will stop at that point. You can then use the F8 to step through the code, one line at a time, to see what path it takes (and if you hover over any variables, it will show you their value at that point in time). I often have the code up on one monitor and my worksheet on the other, so I can watch what it happening to the sheet as I go through my code.

At the end, you can remove the breakpoint simply by clicking on it.

If you could advise on post #4 i think i will just go that way as time isnt on my side & need to press on.

If you are asking how to select the last row in column B with values, you can do this:

Cells(Rows.Count, "B").End(xlUp).Select

 

[ipbr21054]

Unfortuneatly those 5000 posts will all be for help / advise & i thank members for that.
Ive had 50 years of learning issues & doing the same thing today,tomorrow or the next day will mostly all be a different outcome.

So thanks for that line of code above & ive now done the following.
I see the Msgbox asking if there is a photo to hyperlink.
I select Yes, The userform closes & the customers cell is selected but thats it.
The hyperlink part doesnt work ?

If i put the code shown in RED on a button,select the customer & run the code it works.
Now i dont see why it doesnt work with what i done below.

answer = MsgBox("IS THERE A PHOTO TO HYPERLINK ?", vbYesNo + vbInformation, "HYPERLINK PHOTO MESSAGE")
        If answer = vbNo Then
        Exit Sub
        Else
       
        Unload PostageTransferSheet
        Exit Sub
        End If
       
        Cells(Rows.count, "B").End(xlUp).Select
       
       With ActiveCell
         Const FILE_PATH As String = "C:\Users\Ian\Desktop\REMOTES ETC\DR\EBAY CUSTOMERS PHOTOS\"
        If ActiveCell.Column = Columns("B").Column Then
         
        If Len(Dir(FILE_PATH & ActiveCell.Value & ".jpg")) Then
        ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & ActiveCell.Value & ".jpg"
        With ActiveCell
        .Font.Size = 12
        End With
        MsgBox "HYPERLINK WAS SUCCESSFUL.", vbInformation, "POSTAGE SHEET HYPERLINK MESSAGE"
        End If
       
        Else
        MsgBox "PLEASE SELECT A CUSTOMER FIRST TO HYPERLINK THE PHOTO.", vbCritical, "POSTAGE SHEET HYPERLINK MESSAGE"
        Exit Sub
        End If
       
        If Dir(FILE_PATH & ActiveCell.Value & ".jpg") = "" Then
        If MsgBox("THERE IS NO PHOTO FOR THIS CUSTOMER" & vbNewLine & "WOULD YOU LIKE TO OPEN THE PHOTO FOLDER ?", vbYesNo + vbCritical, "HYPERLINK CUSTOMER PHOTO MESSAGE.") = vbYes Then
        CreateObject("Shell.Application").Open ("C:\Users\Ian\Desktop\REMOTES ETC\DR\EBAY CUSTOMERS PHOTOS\")
        End If

        End If
        End With
       
        End Sub