Need a program that will let me do this...

[211]

double click on a value from a long list of frequently used values (call this column 1). When I double click from this list in column 1 it automatically adds it to a second column (column 2) and puts the selection first in a list. When I select another value from column 1 it adds it to column 2 below the first selection made previously, and so on...

Here's what I'm having to do now using Excel.
I have a list of frequently used values, or "events" as we call them. This is a common list who's naming convention never changes. Part of what I do at work is collect Data specific to each one of these events. Problem is, we NEVER collect the data in the same order twice, and may or may not run all the events that are in the list. So when I get back to my desk I have to recreate this list in the order in which I ran the events.

At this point it's a matter of copying from "Column 1" the corresponding event from the list and pasting it to "Column 2" as #1. Then go back to the column 1 list, copy the next run we made and paste it to column 2 as #2. This repeats untill I have a list of events in the order in which they were ran in (some lists grow to the 60's, that's a lot of copying and pasting).

So again, it'd be really nice if I could find a program that would let me build a table or list of these commonly used events, double click an entry in the list, and have it build a second list in the order in which I make the selections. Excel is the closest I've been able to come, but there always seems to be some amount of copying and pasting going on.
Or perhaps I'm not using Excel properly?

I've tried playing with InfoPath this week but it's not quite what I'm looking for. Access is a little deep and seems like overkill for what I'm trying to do. But then again I'm not seasoned with Access at all so maybe it IS the right program but I'm just intimidated by it's complexity.

As far as I've gotten it seems Macros are the only way to go. Problem is, at work I'm unable to toy with macros on an effective level because IT has permissions locked down like a mutha.

Plus I've never really done more than just a two or three keystroke macro anyway so I'm a little scared...

With a little advice, I'd love to create something here at home to bring to work, Id be a HERO!!!

Thanks in advance.

 

[jindon]

If you want to apply the code to every sheets in the workbook then

Open VBE by right click on the Excel icon on the right of File Menu then select View code
Paste the code there and close the window

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
With Target
    If Intersect(Target, Range("g3", Cells(Rows.Count, "h"))) Is Nothing Then Exit Sub
    If .Column = 8 Then x = -1
    Range("b" & Rows.Count).End(xlUp).Offset(1).Resize(, 2).Value = _
    .Offset(, x).Resize(, 2).Value
End With

remove the code that you pasted onto the sheet moduel.

 

[211]

But this code is specific to these rows and columns of interest on this particular worksheet right? So if I find out the worksheet at work is not quite formatted like this one (I.E. the "Event List" lands on different columns than what the code is wrote for) and I post the code to it, it may not work?


Even if I have to re-write the **** worksheet at work I don't care. I'm so stoked right now! You guys have NO idea how much more affective this will be!

 

[211]

1) Right click on the sheet tab of Sheet1 then select View Code
2) Paste the code there and close the window to get back to Excel

d-click on the cell that you want to transfer the data

Oh yeah I got it! Thanks a bunch man, you're my hero!

 

[jindon]

But this code is specific to these rows and columns of interest on this particular worksheet right? So if I find out the worksheet at work is not quite formatted like this one (I.E. the "Event List" lands on different columns than what the code is wrote for) and I post the code to it, it may not work?


Even if I have to re-write the **** worksheet at work I don't care. I'm so stoked right now! You guys have NO idea how much more affective this will be!

What is the Headings of the columns to be transferred and those of to be pasted?

 

[211]

What is the Headings of the columns to be transferred and those of to be pasted?

You're refering to the worsheet we use at work? I don't recall off the top of my head. I think what I'll do though is re write our worksheet to more closely resemble the one I wrote for you. The one I wrote makes more sense anyway. Then we could keep that as a template on our Desktop.

I'm gettin a compile error when I close the VBE "Only Comments May appear after end sub, end function, or end property" I'm pasting the exact code you wrot earlier.

 

[jindon]

OK
You can do that way, too.

paste the code onto ThisWorkbook module
it will find "Standard" to determin the column and row to be transffered
"Run #" to determin the column to be pasted

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim rStandard As Range, rRun As Range, x As Long
Dim rS As Long, rC As Long, rCrun As Long
Set rStandard = Cells.Find("Standard", , , xlPart)
Set rRun = Cells.Find("Run #")
If rStandard Is Nothing Or rRun Is Nothing Then Exit Sub
rS = rStandard.Row + 2: rC = rStandard.Column: rCrun = rRun.Column + 1
With Target
    If Intersect(Target, Range(Cells(rS, rC), Cells(Rows.Count, rC + 1))) Is Nothing Then Exit Sub
    If .Column <> rC Then x = -1
    Cells(Rows.Count, rCrun).End(xlUp).Offset(1).Resize(, 2).Value = _
    .Offset(, x).Resize(, 2).Value
End With
End Sub

 

[211]

Mmmm.
That's not the direction we're trying to go. With that code, when i d-click it pastes duplicate values in the "Run#" list. The first code was perfect becase I could select individual events one at a time.

Also that compile error when I close the VBE "Only Comments May appear after end sub, end function, or end property".

Ive even started from a blank worksheet and still get the error when I open or try and close excel.

BTW I really appreciate your help and patience on this!

 

[XLGibbs]

That error usually comes when you have something written below the End Sub line of the routine that is not "commented out" ...make sure that there is no free text typed below End Sub....

 

[211]

That error usually comes when you have something written below the End Sub line of the routine that is not "commented out" ...make sure that there is no free text typed below End Sub....

Okay, I think I got it. I've commented out several entries in several modules.
Now I've got another error when I open the worksheet:

Error Referencing [VBIDE=VBE6EXT.OLB]

Error Number:=91
Error Discrp:=Object variable or With block variable not set

You may have to manually set a reference to the [VBIDE=VBE6EXT.OLB]

The only thing I did was insert a drop-down box at the bottom of the list. Wasn't quite what I was after so I deleted it (actually I hit the Undo button till it was gone).
Sounds to me like something is still trying to reference that object?
I don't know jack though...

 

[211]

Kay nevermind! I got it; once again I underestimate the power of Google.
Pointed right back to a post on this forum in the HTML Maker FAQs.

Error: "Error Number: 91, Object Variable or with Block variable not set..." Click OK to get rid of the dialog box and go to the Tools->Macros->Security. Click the Trusted Sources tab and check the box next to "trust access to Visual Basic Project," and click OK.

This is definately a start to a very important process at my work. Thanks to everyone that has helped me out with this. YOU ROCK!!!