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

**beacon** · 04-21-2008, 01:39 PM

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

**StephenR** · 04-21-2008, 02:36 PM

Perhaps something like this?

Please Login or Register  to view this content.

**beacon** · 04-21-2008, 03:32 PM

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...

**StephenR** · 04-22-2008, 06:12 AM

Try assigning the buttons to these two macros:

Please Login or Register  to view this content.

**royUK** · 04-22-2008, 06:17 AM

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.

**beacon** · 04-22-2008, 09:48 AM

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.

**beacon** · 04-22-2008, 10:58 AM

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.

**StephenR** · 04-22-2008, 11:42 AM

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.

**beacon** · 04-22-2008, 05:06 PM

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.

**beacon** · 04-22-2008, 07:15 PM

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

**beacon** · 04-23-2008, 11:00 AM

bump

-beacon

**StephenR** · 04-23-2008, 11:14 AM

Try this:

Please Login or Register  to view this content.

**beacon** · 04-23-2008, 01:17 PM

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?

**royUK** · 04-23-2008, 02:45 PM

I believe pseudocode or real code snippets are more readable with Code tags, & much easier to copy.

**beacon** · 04-24-2008, 09:21 AM

bump

-beacon

**StephenR** · 04-24-2008, 10:12 AM

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.

**beacon** · 04-24-2008, 11:02 AM

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...

**StephenR** · 04-24-2008, 12:01 PM

ho hum...will come back later and look at this.

**StephenR** · 04-24-2008, 01:46 PM

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.

**beacon** · 04-24-2008, 02:22 PM

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??

**StephenR** · 04-24-2008, 02:26 PM

I don't understand the logic either but it seems to work for me - see the attachment.

**beacon** · 04-24-2008, 05:49 PM

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?

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

LinkBack

Thread Tools

Rate This Thread

Display

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

Not quite

Same problem

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions