Create MessageBox to promt me to select data in DropBox

[RooSXL]

I am trying to create an excel report that will be gathering information from different files and it will mainly be driven by macros trigerring macros. However there a couple of cells that require some information in order for the macros to pick the right files. How create a macro that will prompt the user to input data in particular? NOTE: Cells have dropboxes in order for the user to only pick valid information. Does anyone know an easy way to do this?

Thanks,

 

[cLFlaVA]

Just use the MessageBox (or MsgBox) function and pass it a prompting statement, along with an if statement to first determine IF there is data:

If Range("A1").Value = "" Then
    MessageBox "Please enter data in cell A1!"
End If

*cLFlaVA
----------------------------
Lois: "Peter, you're drunk!"
Peter: "I'm not drunk, I'm just exhausted from stayin' up all night drinking!

 

[RooSXL]

Thanks. cLFlaVA...

Could prompt this message box in the first macro that is ran
in order for the user to select the date first in the O4 cell? Below is a sample of the macro that fires up first to run the report. Feel free to make any correction necessary to run this more effectively.

Thanks again for your input.

PS> How do you insert the macro in the CODE box in your message?



Sub Get_DataFrom_VarianceBook()
Dim StrFileName As String
Sheets("Month").Select
Range("O4").Select
StrFileName = ActiveCell.Value

Workbooks.Open Filename:= _
"S:\ACCT\CostDept\WD Variances & Recons 2004\Variance Books 2004\" + StrFileName

'Grab the Variance Book Data
Windows(StrFileName).Activate
Sheets(2).Select
Range("F1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=7, Criteria1:="411"
Selection.CurrentRegion.Select
Selection.Copy
Windows("ME Performance Report Plants.xls").Activate
Range("h7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(StrFileName).Activate
ActiveWindow.Close


End Sub
Sub FilterInformation_VarianceBooktoImport()

 

[cLFlaVA]

To get code inside the code block, surround it with [ignore]

[/ignore] tags.


Below is some modified code. I haven't tested it, so sorry if you get an error. Some things to keep in mind: you shouldn't use Activate and Select in Macro code unless completely necessary, as it slows down your code. Also, if you're performing multiple actions on one particular object, you can use a With block. Let me know if you have any questions about the code below.

Sub Get_DataFrom_VarianceBook()
    Dim StrFileName As String
    Dim book as Workbook

    StrFileName = Sheets("Month").Range("O4").Value

    ' if there is no filename in the cell, tell user
    If Len(Trim(StrFileName)) = 0 Then
        Msgbox "Please Enter Filename in O4!"
        End
    End If
    
    Set book = Workbooks.Open("S:\ACCT\CostDept\WD Variances & Recons 2004\Variance Books 2004\" & StrFileName)
    
    'Grab the Variance Book Data

    With book.Sheets(2).Range("F1")
        .AutoFilter
        .AutoFilter Field:=7, Criteria1:="411"
        .CurrentRegion.Select
        .Copy
    End With

    Windows("ME Performance Report Plants.xls").Range("H7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows(StrFileName).Close
End Sub

*cLFlaVA
----------------------------
Lois: "Peter, you're drunk!"
Peter: "I'm not drunk, I'm just exhausted from stayin' up all night drinking!

 

[RooSXL]

It was giving me an error with


[.CurrentRegion.Select
.Copy
End With]

Therefore I took the select out. But then errors out again in

[ Windows("ME Performance Report Plants.xls").Range("H7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False]

What do you think it could be the problem. Thank you very much for the block suggestion, I will certainly begin using it like this.

 

[RooSXL]

CLFlava...

What do you mean by surround it, because I tried to place it

 It doesn't work [\code]

 

[RooSXL]

 Let's see

 

[RooSXL]

 Nevermind....Thanks a mil!!!

 

[Loomah]

Hi Roo
This is one of those situations where you need to activate, in this case the different window. eg

Windows("ME Performance Report Plants.xls")[b].Activate[/b]
Worksheets("YourWorksheetName").Range("H7").PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False

A little note though, it helps us understand what is wrong if you could post the error messages you get

Further to your question on the 'code tags' have a look at the "Process TGML" link at the bottom of the posting window just above the "Submit Post" button to see other available tags.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​