+ Reply to Thread
Results 1 to 22 of 22

Command button to find a cell with a specific value in it

  1. #1
    Registered User
    Join Date
    10-19-2007
    Posts
    21

    Exclamation Command button to find a cell with a specific value in it

    Hi everybody,

    I have a spreadsheet that I'm using as a call log for a support desk. Sometimes, when the problem is outside of my scope, I have to call in a ticket to a helpdesk.

    I want to keep the call log separate from the ticket list, but I want to be able to reference a ticket number for a specific call or vice versa using a command button.

    Here's the psuedocode for my problem:
    Please Login or Register  to view this content.

    I will already have the workbook open with the call log and the ticket list worksheets so there's no need to open a workbook. However, I have 2 additional worksheets in this workbook that don't need to be searched because the data in these two is significantly different than the others and, I feel, would only slow the search time.

    I have some programming experience, but I'm self taught with VBA, so I struggle at times. If the solution for this problem is right in front of my face, please go easy on me.

    Thank you for any help you can toss my way,
    beacon
    Last edited by royUK; 04-22-2008 at 06:19 AM.

  2. #2
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607
    Perhaps something like this?
    Please Login or Register  to view this content.
    Last edited by StephenR; 04-21-2008 at 02:39 PM.

  3. #3
    Registered User
    Join Date
    10-19-2007
    Posts
    21

    Exclamation Not quite

    Hi Stephen,

    I used what you suggested, but the 'b.select' line of code, and everything after, evoked an error message ("Object variable or With block variable not set").

    That may be close to what I was looking for, but let me try detail it a little more to make sure we're on the same page.

    Worksheet 1 - Call Log
    Column A = Call Number
    Value of Column A = 0001 (automatically increments by one for every call)

    Column J = Go To Ticket (Command Button)


    Worksheet 2 - Ticket List
    Column A = Ticket Number (123456)
    Column B = Related Call Number (0001)

    When the user clicks on the command button on the Call Log worksheet, the Ticket List worksheet should activate and the cursor should go to the ticket number for that call number.

    The call number and ticket number are two unique index numbers that will never repeat, so finding multiple instances of them isn't necessary. The first instance should be the only instance.

    Conversely, and this should be fairly easy for me to figure out if I can ever get the first part of the problem working, I would like to place a command button on the Ticket List worksheet that will take me back to the call number on the Call Log worksheet.

    Excel search functions, even with the code you sent me, still have me confused.

    Thanks again for any help you can dish out...
    Last edited by beacon; 04-21-2008 at 03:43 PM.

  4. #4
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607
    Try assigning the buttons to these two macros:
    Please Login or Register  to view this content.

  5. #5
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202
    beacon, please use Code tags when posting code. I will add them this time, if you click edit on the post you will see how they are used.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  6. #6
    Registered User
    Join Date
    10-19-2007
    Posts
    21
    No disrespect, but I would have put code tags around what I typed if I had typed actual code. However, I didn't see anything in the rules about having to or needing to put tags around algorithms or pseudocode.

    If this is necessary, please let me know and I'll be glad to comply in the future, but otherwise I believe that the readability is much easier in this case and that code tags should be reserved for actual, specific code.

  7. #7
    Registered User
    Join Date
    10-19-2007
    Posts
    21
    I'm still getting the same error message as before (object variable or with block variable not set).

    The debug has highlighted the following code:
    Please Login or Register  to view this content.
    Also, I tried to attach my spreadsheet, but it kept sending me to an alternate page that said I followed an invalid link.
    Last edited by beacon; 04-22-2008 at 11:02 AM.

  8. #8
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607
    Probably not finding the search value. Is it a number 1 formatted as 0001 or a string 0001 and is it the same on both sheets?

    Did you click on the "manage attachments" button - a dialogue box should open and then you can find your file and attach.

  9. #9
    Registered User
    Join Date
    10-19-2007
    Posts
    21

    Same problem

    I checked the formatting and it all seems to be the same.

    I used the manage attachments to attach my file, but it keeps going to a page that says I need to contact the administrator. Do you think maybe my job's network settings are preventing it from uploading?

    I'll try and upload when I get to another computer.

  10. #10
    Registered User
    Join Date
    10-19-2007
    Posts
    21
    I finally was able to upload the file to the server at my school. It takes a minute to download because it's like 5MB.

    Maybe this will pull it all together.

    CRS Data Log.xls

  11. #11
    Registered User
    Join Date
    10-19-2007
    Posts
    21
    bump

    -beacon

  12. #12
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607
    Try this:
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    10-19-2007
    Posts
    21
    Did you get that code to work for you on the spreadsheet? I'm putting the GoTo_TicketNumber code in under Sheet1 in the Project Explorer and the GoTo_CallLog under Sheet2.

    When I do that, no I get a new error message. It says:
    Compile Error:

    Object library invalid or contains references to object definitions that could not be found
    Should I be putting your code in a module?

  14. #14
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202
    I believe pseudocode or real code snippets are more readable with Code tags, & much easier to copy.

  15. #15
    Registered User
    Join Date
    10-19-2007
    Posts
    21
    bump

    -beacon

  16. #16
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607
    You need to assign this code to the button on Ticket List sheet:
    Please Login or Register  to view this content.
    and this to a button on the call log sheet.
    Please Login or Register  to view this content.
    Slight modification to previous code.

  17. #17
    Registered User
    Join Date
    10-19-2007
    Posts
    21
    I think I've finally figured out what the problem is. On the call log sheet I have it setup so that when you select the person receiving the call, the call number automatically generates.

    On the ticket list sheet, I have a reference to the appropriate call number cell on the call log sheet so that it copies over to the ticket list.

    Your code works like a charm if I manually type the call number into the ticket list sheet, but not if it's a reference. Is there anyway around this?

    Here's a different explanation.
    Call Log sheet:
    Call Number Employee
    0001 A. Smith
    0002 B. Smith
    0003 C. Smith

    Ticket List sheet:
    Ticket Number Call Number
    123456 0001 (='CRS Call Log'!A2)
    123457 0002 (='CRS Call Log'!A3)
    123458 0003 (='CRS Call Log'!A4)
    Because I'm using the function to populate the call number on the second sheet, I'm guessing something needs to be adjusted on the code, but I don't know what.

    Thanks again for all your help and diligence...

  18. #18
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607
    ho hum...will come back later and look at this.

  19. #19
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607
    Stumbling around in the dark a little here but it appears to be connected to the special formatting. This code for the call log sheet appears to work:
    Please Login or Register  to view this content.

  20. #20
    Registered User
    Join Date
    10-19-2007
    Posts
    21
    The new code isn't working for me if I keep the cell reference on the ticket list sheet. I keep getting the object variable not set error (with the application.goto highlighted) from before.

    If I remove it, then everything works fine, but it kind of defeats the purpose of having an index to each of the ticket numbers.

    I'm not 100% on this, but I think that ActiveCell.Text doesn't want to work because it's a string value and the value in the 'a' is a formatted number.

    Any ideas??

  21. #21
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607
    I don't understand the logic either but it seems to work for me - see the attachment.
    Attached Files Attached Files

  22. #22
    Registered User
    Join Date
    10-19-2007
    Posts
    21
    Ok, everything is working perfectly now.

    I have one last question and I'll leave this forum alone for awhile. For the GoTo_TicketNumber command button, can I copy the button multiples times, but still have it refer to the same routine as the original?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1