Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
bfuzzeq
 
Posts: n/a
Default macro to create lists


Hi,

I have a spreadsheet where each row contains anywhere from 1 to 8 names
($G$4:$N$4). I need to combine these names into a list box for use in a
pivot chart. I know how to create the list box using data validation,
and I recorded a macro while doing this, but the macro is too specific.
It just repeats the first data selection range. I need the macro to be
variable depending on the row ($G$5:$N$5, $G$6:$N$6, $G$7:$N$7, ...).
Obviously, I have no VB Coding experience and my Excel skills are very
basic. I spent a couple hours sifting through messages and either
haven't found what I'm looking for, or didn't understand what I was
looking at. Any help would be greatly apreciated.

Sincerely,

Bfuzzeq

If it helps here is the macro I generated:


Code:
--------------------
Sub Macro7()
Application.CutCopyMode = False
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$G$4:$N$4"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
ActiveWindow.SmallScroll ToRight:=4
End Sub
--------------------


--
bfuzzeq
------------------------------------------------------------------------
bfuzzeq's Profile: http://www.excelforum.com/member.php...o&userid=24758
View this thread: http://www.excelforum.com/showthread...hreadid=383299

  #2   Report Post  
bfuzzeq
 
Posts: n/a
Default


I discovered that posting the drop down list in the pivot chart was not
as simple as I thought so I endned up using a solution I saw in another
Post, create another column with the formula =H2&", "&B2 ... This works
well enough and the names transfer over to the pivot chart which is
what matters., but I have a different problem in that the pivot chart
keeps creating 'total' rows for each column at each line entry. It's a
little difficult to explain so I'm attaching the file (pdf). How can I
get rid of these extra lines and the "Totals"?


+-------------------------------------------------------------------+
|Filename: cme_062905b.pdf |
|Download: http://www.excelforum.com/attachment.php?postid=3549 |
+-------------------------------------------------------------------+

--
bfuzzeq
------------------------------------------------------------------------
bfuzzeq's Profile: http://www.excelforum.com/member.php...o&userid=24758
View this thread: http://www.excelforum.com/showthread...hreadid=383299

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to create a command button, then assign macro to it in excel kshaheen Excel Discussion (Misc queries) 3 June 24th 08 01:03 PM
How do I create a macro, that references another excel file ??? Aries Excel Worksheet Functions 1 May 25th 05 11:37 AM
How do I create a macro to return drop down boxes to first item i. gaalseth Excel Discussion (Misc queries) 1 April 13th 05 09:09 PM
Macro to create macro Carl Bowman Excel Discussion (Misc queries) 2 February 6th 05 11:49 AM
How do I create a macro of editing keystrokes? scjanner Excel Discussion (Misc queries) 0 January 22nd 05 12:07 AM


All times are GMT +1. The time now is 01:15 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"