[RESOLVED] Finding a vlaue and returning to MSGBOX, i have some code already.

[hensucker]

Hello i am trying to find a value from a cell in on one sheet, the user would press a command button which will then run the code to look in another sheet for the data, Should it find a match it will return data from the line it found the match. and show it in a message box, the message box will then ask if the user wants to delete, if they choose yes it will wipe the data on the whole line it found the match.
I have the code but the message box doesnt display any returned data, and it wont delete any data either please help!

[hensucker]

Sub commandbutton1_click()
 
Dim shtarr(1) As String, s As Integer
shtarr(0) = "Diary"
sheetname = shtarr(s)
For s = 0 To UBound(shtarr)
Set r = Sheets(shtarr(s)).UsedRange.Find(Sheets("DSA").Range("B2").Value)            '<--check
If Not r Is Nothing Then   ' found
Dim msg1 As String
Dim response1
' you need to select case, to assign variables before trying to use them n the msgbox
Select Case r.Column                     '<--select case for return value
End Select
msg1 = "The customer matching NINO: " & UsedRange.Text _
& vbCrLf & vbCrLf & "Already has an appointment on: " & r.Offset(0, -2) & r.Offset(0, -1) _
& vbCrLf & vbCrLf & "For: " & r.Offset(0, 3) & " with: " & r.Offset(0, 5) _
& vbCrLf & vbCrLf & "This appointment is: " & r.Offset(0, 4) & "And was booked by: " & r.Offset(0, 6) _
& vbCrLf & vbCrLf & "Would you like to delete this appointment?"
response1 = MsgBox(msg1, vbExclamation + vbYesNo)
If response1 = vbYes Then
If Not r Is Nothing Then Range(r, r.Offset(0, 6)).ClearContents
ElseIf response1 = vbNo Then
MsgBox "No Details Where Changed, Your Input Will Be Cleared", vbInformation + vbOKOnly
Exit Sub
Else
MsgBox "No Data"
End If
End If
Next
End Sub

[westconn1]

with no indenting your code is very hard to follow
you have a select case that is doing nothing at all
i doubt that usedrange.text is valid without a sheet object, or what you actually would want in your message
you should not need a second if not r is nothing as you are already within that condition
you probably want
r.entirerow.clearcontents (or r.entirerow.delete)

[hensucker]

The usedrange.text is meant to be what the user has typed to search for, the select case was meant to be removed. I am really bad for not using indents as im sure your aware by now also r.entirerow.clearcontents or r.entirerow.delete would work as the first 2 columns on the holding sheets are protected and must remain intact, so i need 3 to 8 only clearing.

[hensucker]

Hello Pete or anyone in fact, i have been messing with the code and re-arranged and hopefully indented it properly well the best i can anyway, now i still cant get a message box to display certain information from the found row and i cant get it to clear contents only in columns c to k, leaving column A and B untouched.
Here is the code can you give any pointers?

Private Sub commandbutton1_Click()
 
    Dim vOppLocation As Variant
    Dim c As Variant
    Dim d As Variant
    Dim e As Variant
    Dim f As Variant
    Dim g As Variant
    Dim h As Variant
    Dim i As Variant
    Dim j As Variant
    Dim k As Variant
        
    'Set c = Sheets("Diary").Range("c").Value
    'Set d = Sheets("Diary").Range("d").Value
    'Set e = Sheets("Diary").Range("e").Value
    'Set f = Sheets("Diary").Range("f").Value
    'Set g = Sheets("Diary").Range("g").Value
    'Set h = Sheets("Diary").Range("h").Value
    'Set i = Sheets("Diary").Range("i").Value
    'Set j = Sheets("Diary").Range("j").Value
    'Set k = Sheets("Diary").Range("k").Value
    
    ' -- Check a number has been completed
    If Sheets("DSA").Range("B2") = "" Then
    
        MsgBox "Please enter a Ref Number to find.", vbCritical + vbOKOnly, "Reference Number"
        Sheets("DSA").Range("B2").Select
        Exit Sub
        
    End If
    
    ' -- Search for reference number
    Set vOppLocation = Sheets("Diary").Range("E:E").Find(Sheets("DSA").Range("B2"), LookIn:=xlValues, LookAt:=xlWhole)
    
    If Not vOppLocation Is Nothing Then
    
        ' -- Opp found, Question User
        MsgBox "A Match for : " & Sheets("DSA").Range("B2").Value & " was found" _
        & vbCr & "Would you like to delete it?", _
        vbInformation + vbYesNo, _
        "Found"
 
        Sheets("Diary").Range(vOppLocation.Row & c, k).ClearContents
        ' -- Clear search box
        Sheets("DSA").Range("B2") = ""
    
        MsgBox "The Ref Number" & sheets("DSA").range("B2").value & "removed.", vbInformation +      vbOKOnly, "Remove Ref"
        Sheets("DSA").Range("B2").Select
   
    Else
    
        ' -- Opp not found
        MsgBox "Nothing was found for :  " & Sheets("DSA").Range("B2").Value _
        & vbCrLf & "Please check your input or presume it doesnt exist.", _
        vbCritical + vbOKOnly, _
        "Opportunity Not Found"
            
    End If
End Sub

[westconn1]

i have been messing with the code and re-arranged and hopefully indented it properly well the best i can anyway, now i still cant get a message box to display certain information from the found row

which msgbox do show?
if "a match for" is not showing i would assume that the reference number is not found

i can only suggest you post a sample workbook (zip first) xl 2003 version, with some data and your code and i will test

[incidentals]

you want a form that asks for a matchable thing and it asks if found whether or not you wish to delete it from the other sheet....


do your really mean you want a deletion process that searches for a given thing and deletes it if found, after confirmation?

[hensucker]

Yes i have the code that will find a match and also tell me if it didnt find a match, Now the message box that prompts the user if they wish to delete, wants to consist of data from the row it finds the match on.
such as:

MsgBox "A Match for : " & Sheets("DSA").Range("B2").Value & " was found" _
        & vbCr & "Currently the appointment stands at" & Column1 & Column2 & "if these details are wrong would you like to delete?", _
        vbInformation + vbYesNo, _
        "Found"

So the message box would display the current details for the match, if the user doesnt want to keep the data they can press yes to clear, it would then only clearcontents of column C to K on the matched row.

[hensucker]

So i have been tweaking and this is sort of the direction i believe is correct, but i cant seem to get the r.offset function to work?
I think i have to declare the col variable somewhere.

MsgBox "A Match for : " & vOppLocation & " was found" _
        & vbCr & "The Current Appointment is on" & vOppLocation(r.Offset(col, -4)) & "at time" & vOppLocation(r.Offset(col, -3)) _
        & vbCr & "Would you like to delete it?", _
        vbInformation + vbYesNo, _
        "Found"

As you can see vopplocation is referred to as where it finds the match, then on that same row im trying to get the offsets of other columns that match this search, so the user can see what they have looked for before deleting.

[hensucker]

Ok i have managed to get there myself thank you anyways but if i could just get a small amount of help before i mark resolved, here is the code as follows and i will explain below.

Private Sub commandbutton1_Click()
 
    Dim vOppLocation As Variant
 
    
    ' -- Check a number has been completed
    If Sheets("DSA").Range("B2") = "" Then
    
        MsgBox "Please enter a ref Number to find.", vbCritical + vbOKOnly, "Reference Number"
        Sheets("DSA").Range("B2").Select
        Exit Sub
        
    End If
    
    ' -- Search for reference number
    Set vOppLocation = Sheets("Diary").Range("E:E").Find(Sheets("DSA").Range("B2"), LookIn:=xlValues, LookAt:=xlWhole)
    If Not vOppLocation Is Nothing Then
    
        ' -- Opp found, Question User
        MsgBox "You Have Searched for : " & vOppLocation & " and the following data was returned" _
        & vbCrLf & "Customers Name : " & vOppLocation.Offset(0, -2) & " " & vOppLocation.Offset(0, -1) _
        & vbCrLf & "The Current Appointment Date is : " & vOppLocation.Offset(0, -4) _
        & vbCrLf & "The Current Appointment Time is : " & vOppLocation.Offset(0, -3) _
        & vbCrLf & "The Customers D.O.B is : " & vOppLocation.Offset(0, 1) _
        & vbCrLf & "The Customers Telephone Number is : " & vOppLocation.Offset(0, 2) _
        & vbCrLf & "The Customers Eligibility is : " & vOppLocation.Offset(0, 3) _
        & vbCrLf & "Support Needed by the Customer : " & vOppLocation.Offset(0, 4) _
        & vbCrLf & "The Current Contract Holder is : " & vOppLocation.Offset(0, 5) _
        & vbCrLf & "The Original Referrer is : " & vOppLocation.Offset(0, 6), _
        vbInformation + vbYesNo, _
        "Found"
 
        'vOppLocation.Offset(0, -2).ClearContents
        'vOppLocation.Offset(0, -1).ClearContents
        'vOppLocation.Offset(0, 0).ClearContents
        'vOppLocation.Offset(0, 1).ClearContents
        'vOppLocation.Offset(0, 2).ClearContents
        'vOppLocation.Offset(0, 3).ClearContents
        'vOppLocation.Offset(0, 4).ClearContents
        'vOppLocation.Offset(0, 5).ClearContents
        'vOppLocation.Offset(0, 6).ClearContents
        
        ' -- Clear search box
 
    
        MsgBox "All appointments and details matching " & Sheets("DSA").Range("B2").Value & " have been removed", vbInformation + vbOKOnly, "Remove Opportunity"
        ElseIf vbYesNo = vbNo Then
        MsgBox "Nothing Removed"
        Exit Sub
        Sheets("DSA").Range("B2") = ""
        Sheets("DSA").Range("B2").Select
   
    Else
    
        ' -- Opp not found
        MsgBox "Nothing was found for :  " & Sheets("DSA").Range("B2").Value _
        & vbCrLf & "Please check your input or presume it doesnt exist.", _
        vbCritical + vbOKOnly, _
        "Opportunity Not Found"
            
    End If
End Sub

Ok so the code searches and if it finds a match it returns the data and all the offsets as i want them, if the users clicks yes it will wipe the data for them, i have place this part as comments for now whilst i get it fully operational.
When the user clicks no i want it to display the msgbox i incorporated after the elseif code but im not sure if that is correct.
and one last thing the line

& vbCrLf & "The Current Appointment Time is : " & vOppLocation.Offset(0, -3) _

needs to be formatted as time
and the line

& vbCrLf & "The Customers Telephone Number is : " & vOppLocation.Offset(0, 2) _

needs to be formatted as an 11 digit number so it has the leading zero.

[westconn1]

for the last 2 use the format function
like

& vbCrLf & "The Current Appointment Time is : " & format(vOppLocation.Offset(0, -3), "hh:mm:ss") _

[hensucker]

Thank you pete, any help on if the user clicks no?
At the moment it just runs the procedure as if the user said yes ?

[incidentals]

you do not have a place where the user can click no - as far as i can see all you have are msgboxes set as vbokonly

and the else if yes=no is a complete (well i said i would be good today) mess

you need to change the message boxes into functions and work with the returned value...

simply wrap the message in () and set the button type to at least vbyesno

the use like this

ans=msgbox("hello mum",vbyesno) fill tis in properly!
if ans=vbyes then
else
end if

is that any clearer for you?

here to help

[hensucker]

As you can see this message box contains the vbyesno function.

' -- Opp found, Question User
        MsgBox "You Have Searched for : " & vOppLocation & " and the following data was returned" _
        & vbCrLf & "Customers Name : " & vOppLocation.Offset(0, -2) & " " & vOppLocation.Offset(0, -1) _
        & vbCrLf & "The Current Appointment Date is : " & vOppLocation.Offset(0, -4) _
        & vbCrLf & "The Current Appointment Time is : " & vOppLocation.Offset(0, -3) _
        & vbCrLf & "The Customers D.O.B is : " & vOppLocation.Offset(0, 1) _
        & vbCrLf & "The Customers Telephone Number is : " & vOppLocation.Offset(0, 2) _
        & vbCrLf & "The Customers Eligibility is : " & vOppLocation.Offset(0, 3) _
        & vbCrLf & "Support Needed by the Customer : " & vOppLocation.Offset(0, 4) _
        & vbCrLf & "The Current Contract Holder is : " & vOppLocation.Offset(0, 5) _
        & vbCrLf & "The Original Referrer is : " & vOppLocation.Offset(0, 6), _
        vbInformation + vbYesNo, _
        "Found"

It just does nothing if the user clicks no..

Thank you pete for your time format code

[westconn1]

It just does nothing if the user clicks no..

you do not use the return from the messagebox to know what the user clicked

see incidentals post

[hensucker]

Ok i have use the functions and everything seems to run fine, i get the nothing removed when i click no and i get the nothing found if a match isnt found, my only issue now is when i do the initial command button click and it find a match, just a blank yesno box appears with none of the description typed up, what have i possibly missed as it doesnt give any error?

Dim msg1 As String
    Dim response1
    
        ' -- Opp found, Question User
        response1 = MsgBox(msg1, vbInformation + vbYesNo)
        msg1 = "You Have Searched for : " & vOppLocation & " and the following data was returned" _
            & vbCrLf & vbCrLf & "Customers Name : " & vbTab & vOppLocation.Offset(0, -2) & " " & vOppLocation.Offset(0, -1) _
            & vbCrLf & vbCrLf & "The Current Appointment Date is : " & vbTab & vOppLocation.Offset(0, -4) _
            & vbCrLf & vbCrLf & "The Current Appointment Time is : " & vbTab & Format(vOppLocation.Offset(0, -3), "h:mm") _
            & vbCrLf & vbCrLf & "The Customers D.O.B is : " & vbTab & vOppLocation.Offset(0, 1) _
            & vbCrLf & vbCrLf & "The Customers Telephone Number is : " & vbTab & Format(vOppLocation.Offset(0, 2), "00000000000") _
            & vbCrLf & vbCrLf & "The Customers Eligibility is : " & vbTab & vOppLocation.Offset(0, 3) _
            & vbCrLf & vbCrLf & "Support Needed by the Customer : " & vbTab & vOppLocation.Offset(0, 4) _
            & vbCrLf & vbCrLf & "The Current Contract Holder is : " & vbTab & vOppLocation.Offset(0, 5) _
            & vbCrLf & vbCrLf & "The Original Referrer is : " & vbTab & vOppLocation.Offset(0, 6)
 
    If response1 = vbYes Then
            'vOppLocation.Offset(0, -2).ClearContents
            'vOppLocation.Offset(0, -1).ClearContents
            'vOppLocation.Offset(0, 0).ClearContents
            'vOppLocation.Offset(0, 1).ClearContents
            'vOppLocation.Offset(0, 2).ClearContents
            'vOppLocation.Offset(0, 3).ClearContents
            'vOppLocation.Offset(0, 4).ClearContents
            'vOppLocation.Offset(0, 5).ClearContents
            'vOppLocation.Offset(0, 6).ClearContents
            
            MsgBox "All appointments and details matching " & Sheets("DSA").Range("B2").Value & " have been removed", vbInformation + vbOKOnly, "Remove Opportunity"
    ElseIf response1 = vbNo Then
            MsgBox "No Details were removed"
    Exit Sub
    End If
    
        Sheets("DSA").Range("B2") = ""
        Sheets("DSA").Range("B2").Select
   
    Else
    
        ' -- Opp not found
            MsgBox "Nothing has been found for : " & Sheets("DSA").Range("B2").Value _
            & vbCr & "Please check your entry or presume it does not exist", _
            vbExclamation + vbOKOnly, _
            "Referral Not Found"
            
    End If
End Sub

[hensucker]

Dont worry guys i have sorted the issue i misplaced a line of code, this one

response1 = MsgBox(msg1, vbInformation + vbYesNo)

The entire thing works as it should now, thank you for all your help thread resolved.