My movie list

[Andrew]

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

 

[Cecilkumara Fernando]

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
cliped

 

[Bob Phillips]

For the first part, add a custom list (Tools>Options>Custom Lists), and sort
by that(Data>Sort>Options)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)