Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
My movie list
Ladies & Gents,
With the help of some people here, I have been able to write a nice little Movie List macro to help me catalogue my movies. It counts my movies, adds the titles, the "box set" details, the year it was produced, and the date of purchase. It then sorts the movies by the Box Set, the Title and then the year, while disregarding the words "The" & "And". It works fine, except for 3 problems. The first one is not a "biggie", it's just annoying. If I add more than 10 movies for a box set (the box set column also includes the disc number), the movies are sorted as "Disc 1", "Disc 10", 2, 3 etc. Now this only happens with the James Bond Box set, and I don't expect to be buying any large box sets, but it is annoying. Is there a way to sort this column, without having to add a leading 0? The second problem is that I have to type the details for each individual movie, as well as the box set title. How do I add a combo box which will allow me to select a title from the "Box Set" column, without showing the disc # (The Adventures of Indiana Jones - Disc 1), and not repeating titles? Finally, and this is the one that's been knocking me out trying to fix it. How do I add the purchase date to an InputBox, and have it formatted as "dd/mm/yy"? It always adds it as "mm/dd/yy" I've copied a sample of the code I'm using, and marked the problem area with '*'. I've tried setting the date as a string, & MovDate as Date, but I can't seem to set the format. Any assistance would be greatly appreciated Andrew BTW, I feel silly doing this, considering the level of programming skill here on this newsgroup, but I would be happy to share this macro with anyone who's interested. Just reply to this post, and I'll send you a copy back. :-) Sub Insert_Details() Dim ansA As String Dim ansB1 As String Dim ansB2 As String Dim ansC As String Dim ansD As String ' This starts entering the details Range("A2").Select Selection.EntireRow.Insert Rows("2:2").Select Selection.Font.Bold = False ' Movie Title Range("A2").Select ansA = InputBox("What is the movie's title?", "Andrew's Movie List - The Title") ActiveCell.FormulaR1C1 = ansA ' Box set? YesNo = MsgBox("Is it part of a Box Set?", vbYesNo, "Andrew's Movie List - Box Sets") Select Case YesNo Case vbYes Range("B2").Select ansB1 = InputBox("What is the title of the Box Set?", "Andrew's Movie List - Box Sets") ansB2 = InputBox("Which disc of the set?", "Andrew's Movie List - Box Sets") ActiveCell.FormulaR1C1 = ansB1 + " - Disc " + ansB2 Case vbNo 'Insert your code here if No is clicked End Select ' Year? Range("C2").Select ansC = InputBox("What year was the movie made?", "Andrew's Movie List - The Year") ActiveCell.FormulaR1C1 = ansC ' Date of purchase YesNo = MsgBox("Did you purchase the movie today?", vbYesNo, "Andrew's Movie List - Date of Purchase") Select Case YesNo Case vbYes Range("D2").Select ActiveCell.FormulaR1C1 = Format(Date, "dd/mm/yy") * Case vbNo * Range("D2").Select * ansD = InputBox("When did you purchase the movie? - dd/mm/yy", "Andrew's Movie List - Date of Purchase") * ActiveCell.FormulaR1C1 = Format(ansD, "dd/mm/yy") End Select |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
My movie list
Andrew,
for the last part change this * ActiveCell.FormulaR1C1 = Format(ansD, "dd/mm/yy") to this With ActiveCell ..FormulaR1C1 = CDate(ansD) ..NumberFormat = "dd/mm/yy;@" End With HTH Cecil "Andrew" wrote in message ... cliped Finally, and this is the one that's been knocking me out trying to fix it. How do I add the purchase date to an InputBox, and have it formatted as "dd/mm/yy"? It always adds it as "mm/dd/yy" I've copied a sample of the code I'm using, and marked the problem area with '*'. I've tried setting the date as a string, & MovDate as Date, but I can't seem to set the format. ' Date of purchase YesNo = MsgBox("Did you purchase the movie today?", vbYesNo, "Andrew's Movie List - Date of Purchase") Select Case YesNo Case vbYes Range("D2").Select ActiveCell.FormulaR1C1 = Format(Date, "dd/mm/yy") * Case vbNo * Range("D2").Select * ansD = InputBox("When did you purchase the movie? - dd/mm/yy", "Andrew's Movie List - Date of Purchase") * ActiveCell.FormulaR1C1 = Format(ansD, "dd/mm/yy") End Select |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
My movie list
For the first part, add a custom list (ToolsOptionsCustom Lists), and sort
by that(DataSortOptions) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote in message ... Andrew, for the last part change this * ActiveCell.FormulaR1C1 = Format(ansD, "dd/mm/yy") to this With ActiveCell .FormulaR1C1 = CDate(ansD) .NumberFormat = "dd/mm/yy;@" End With HTH Cecil "Andrew" wrote in message ... cliped Finally, and this is the one that's been knocking me out trying to fix it. How do I add the purchase date to an InputBox, and have it formatted as "dd/mm/yy"? It always adds it as "mm/dd/yy" I've copied a sample of the code I'm using, and marked the problem area with '*'. I've tried setting the date as a string, & MovDate as Date, but I can't seem to set the format. ' Date of purchase YesNo = MsgBox("Did you purchase the movie today?", vbYesNo, "Andrew's Movie List - Date of Purchase") Select Case YesNo Case vbYes Range("D2").Select ActiveCell.FormulaR1C1 = Format(Date, "dd/mm/yy") * Case vbNo * Range("D2").Select * ansD = InputBox("When did you purchase the movie? - dd/mm/yy", "Andrew's Movie List - Date of Purchase") * ActiveCell.FormulaR1C1 = Format(ansD, "dd/mm/yy") End Select |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I put movie titles in alphabetical order | New Users to Excel | |||
Autofilter for movie database | Excel Discussion (Misc queries) | |||
Help with Outlook & a movie list | Excel Programming | |||
Help with Outlook & a movie list | Excel Programming | |||
Active movie control | Excel Programming |