Help Form IF Statement Query 1

[robcarr]

Dear All,
I am using a form to select a date via a the calendar control and a report to run and then the user presses the button and the report runs for the date and reports selected, how can I get the coding to show a message if the following statements are true, "no date selected", and "no report selected" and also "date is greater than current date". I would then like the coding to retunr to the form. The coding I am using is below, I think I can get the coding to work for 1 if, but not the 3 that I require above.
'coding used
Private Sub dailyreportrun_Click()
Dim value As Integer
Sheets("personal&quot.Select
[C1] = Dailyreports.value '= this is a combobox
Values = [a1]
[C2] = TBdaily.Text 'this is a text box, calendar value goes in here
Unload Me
value = MsgBox("Report Selected : " & Dailyreports.value & " for the " _
& TBdaily, vbYesNo)
If value = 6 Then
Unload Me
Else
Mainform.Show
End If
reportselect = Dailyreports
Select Case reportselect
Case "Extract Report": Call dailyextract
Case "Sql Report": Call sqldaily
Case "Refund Report": Call dailyrefund
Case "Email Agent Stats": Call getemaildata
Case "Update Client Daily": Call Clientdaily
Case "Update Broadband Report": Call Broadband_Data
Case "Email Data To Be Sent": Call dialler_call
Case "": Exit Sub
End Select
Exit Sub
Mainform.Show
End Sub

Thanks for any help on this.

Rob.

 

[xlbo]

Withiout any other amendments, here's how you could code the IF statements:

Private Sub dailyreportrun_Click()
Dim value As Integer
'Start check
If DailyReports.text = "" or TBDaily.text = "" or datevalue(TBDaily.text) > datevalue(now()) then
msgbox "No no no, that just won't do"
exit sub
else
end if
'end check
Sheets("personal&quot.Select
[C1] = Dailyreports.value '= this is a combobox
Values = [a1]
[C2] = TBdaily.Text 'this is a text box, calendar value goes in here
Unload Me
value = MsgBox("Report Selected : " & Dailyreports.value & " for the " _
& TBdaily, vbYesNo)
If value = 6 Then
Unload Me
Else
Mainform.Show
End If
reportselect = Dailyreports
Select Case reportselect
Case "Extract Report": Call dailyextract
Case "Sql Report": Call sqldaily
Case "Refund Report": Call dailyrefund
Case "Email Agent Stats": Call getemaildata
Case "Update Client Daily": Call Clientdaily
Case "Update Broadband Report": Call Broadband_Data
Case "Email Data To Be Sent": Call dialler_call
Case "": Exit Sub
End Select
Exit Sub
Mainform.Show
End Sub

Rgds
~Geoff~

 

[robcarr]

thanks for doing that for me. I have added a star to the thread. How are you on coding in Outlook, I have this coding below and it should find all files from BTI Reporter and then save each as a zip file(format received is zip). I get currently 10 files a day, but it only saves 2 or 3 a day,and then exits the For Next. Do you know why this is.

Thanks for all your help so far.

Sub Saveattachments()
Dim oApp As Application
Dim oNS As NameSpace
Dim oMsg As Object
Dim oAttachments As Outlook.Attachments
Dim strControl
Set oApp = New Outlook.Application
Set oNS = oApp.GetNamespace("MAPI&quot
Set myInbox = oNS.GetDefaultFolder(olFolderInbox)
Set oFolder = myInbox.Folders("Report Files&quot

strControl = 0

For Each oMsg In oFolder.Items
With oMsg
If .SenderName = "BTI Reporter" Then
strControl = strControl + 1
oMsg.Attachments.Item(1).SaveAsFile "H:\" & strControl & "report.zip"
.Delete
End If
End With
Next
End Sub

 

[rccline]

Rob:

Perhaps you could help me get started on this. I am new to VBA.

I have a range of cells in column A1:A50 with mixed data. Some cells are integers are some are text. I am trying to normalize this database. I want to create VBA that will return the integers to B1:B50 and the text values to C1:C50.

The actual problem is more complex. I am hoping at least, this will get me started.

Record 1 Text
Record 1 Integer
Record 2 Text
Record 2 Interger


Thanks.

Robert

 

[xlbo]

Hi Rob - sorry for the delay - been on holiday
Your loop looks ok - I would guess that either the files are not all there when you run the code or that the name of the sender is not the same as in the code
Rgds
~Geoff~