HELP!!!!! ON CREATING A DIALOG BOX WHEN I RUN A MACRO 3

[lvr5555]

Please need help in VBA. When i run a macro how can i open a dialog box with some data.

need urgent.
thanks

 

[ettienne]

If you mean a message box then:
Msgbox "This is a message box.",vbYesNo+vbInformation,"Message box"

There is also an InputBox as well as user forms, so depending on what you really want you can use one of the 3.

 

[lvr5555]

Thanks it works.

Here is my issue, I have some code in the macro and now i have added the msgbox. so when i run this macro it just shows the msg box and runs the macro as usual. but what i need is after i run the macro it should show the msgbox and it should open the macro though the vba and after i change some thing it should run again.

i hope u get my point.

 

[mintjulep]

i hope u get my point.

Not really.

 

[lvr5555]

And actually here is my overall scenario.

I have to do some changes in the excel daily, so the files i extract daily have a certain date like 1 sep 2008, 2 sep 2008. so i import these files and do some coulmn and row changes and rename the sheets accordingly like a 1 sep 2008 to sheet 1, b 1 sep 2008 to sheet 2 and so on. and then i save th e whole wook book as all 1 sep 2008. so the file import and the sheets and the whole work book has the same name.

so what i did is when creating a marco i took a single file and did all the changes in it and saved the macro.
so for my case if i need to do the same for sep 2 2008 i first need to open the macro change the file name to 2 sep 2008 save the macro and run the macro. which is waste of time as i need to do this on a daily basis.

can some one help me how can i do this in a easier way.

thanks in advance

 

[PHV]

Please, read carefully this:
faq181-2886

 

[lvr5555]

hello mintjulep,

i hope he next message helps.

 

[mintjulep]

You need a user form with a text box (or maybe a date-picker control) and a command button, and a variable.

 

[lvr5555]

Thanks for the quick reply mintjulep.

Actually i looked for some forums as u mentioned but no luck, can u foward me some link if u have any.

thank u.

 

[mintjulep]

A link, sure.

faq707-5689

 

[PHV]

open the macro change the file name to 2 sep 2008
Have a look to the Format function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lvr5555]

Thanks, PHV.

As i have mentioned in the previous message i have the option of changing the date manually, but as this needs to done daily so i want to omit this and do some this automatic. like when i run the macro it pops a promt asking for certain date and it replaces the dates in the macro and then the macro runs accordingly for that date.

And please dont send those tips on how to post a question.

I have read them and now i know how to post one.

thanks for ur consideration.

 

[PHV]

What is YOUR actual code and where are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lvr5555]

I am not stuck, the marco i did runs but the problem is on how to run the marco for daily. i need to change the date every day. so some thing like a prompt asking for a certain date would help.
please see the code below.

Sub t()
'
MsgBox "Please change the dates 10 times (6 times for the original report file, 3 times for sheets and 1 time for the actual work book file"
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\..\S_07Oct2008.csv" _
, Destination:=Range("A1"))
.Name = "S_07Oct2008"
\
\
code to change from .csv to .xsl
\
\

End With
Columns("B:F").Select
Selection.ClearContents
Columns("G:Z").Select
Selection.Cut Destination:=Columns("B:U")
Range("A1").Select
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "S Oct 1 2008"
Sheets("Sheet2").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\..S_07Oct2008.csv" _
, Destination:=Range("A1"))
.Name = "b_S_07Oct2008"
\
\
code to change from .csv to .xsl
\
\
End With
Columns("C:C").Select
Selection.ClearContents
Columns("D:Z").Select
Selection.Cut Destination:=Columns("C:Y")
Range("A1").Select
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "B Oct 1 2008"
Sheets("Sheet3").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\..\u_S_07Oct2008.csv" _
, Destination:=Range("A1"))
.Name = "u_S_07Oct2008"
\
\
code to change from .csv to .xsl
\
\
End With
Columns("G:G").Select
Selection.ClearContents
Columns("H:Z").Select
Selection.Cut Destination:=Columns("G:Y")
Columns("G:Y").Select
Range("A1").Select
Range("A110000").Sort Key1:=Range("D2"), Order1:=xlAscending, Key2:=Range _
("B2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
Selection.EntireColumn.Insert
Columns("E:E").Select
Selection.Cut Destination:=Columns("A:A")
Columns("F:Y").Select
Selection.Cut Destination:=Columns("E:X")
Columns("E:X").Select
Range("A1").Select
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "U Oct 1 2008"
Range("A1").Select
ChDir _
"C:\..\A p"
ActiveWorkbook.SaveAs Filename:= _
"C:\..\A p\ACA S October 01 2008.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub


Above is the code.
I have included the msgbox which says i need to change the date 10 times.
but when i click the ok button in the msgbox, it still runs the macro. what i need it a promt which asks to change the date and it replaces 10 times in the macro and runs it accordigly to that date.

I hope this helps u understange my req.

Thanks in advance

 

[PHV]

Have a look to InputBox

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gavona]

OK this code will prompt you for the date, using yesterday's as the default.

Sub test()
Dim myStr As String
myStr = InputBox("Input Date", "Title", Application.Text(Now() - 1, "DD_MMMMM_yy"))
Range("A1").Value = myStr
End Sub

Gavin

 

[Gavona]

Also: You can simplify the code (and make it run quicker)
Columns("B:F").Select
Selection.ClearContents
becomes
Columns("B:F").ClearContents

Gavin

 

[lvr5555]

Thanks Gavona, this input box helps.

but see i want to replace the date i give in the input field with the ones i have in the macro(see above i.e i nee d to change the date 10 times) so that it will run the macro for that date and saves the whole workbook for that date.

if i add the code u gave it asks for the input date(with default date -- (today's date -1)) then it replaces the date i enter in the A1 and then displays the msg box i have in the above code and then it is running the macro as usual and it is showing the date in the sheet 1 (that is now renamed to s 1 oct 2008) but not in A1 but at last as bcoz its due to the macro steps.

please see the above code so that u will get my point.

Thanks in advance

 

[lvr5555]

any ideas please....

 

[mintjulep]

Dim myPath as String
Dim myDate as String
Dim myFileName as String

MyPath = "C:\..\A p\ACA S "

MyDate = Inputbox("What Date?")

MyFileName = MyPath & MyDate

SaveAs FileName:= myFileName