I have a table called "Parameters" in Access 2010 that contains a ranges of years by Category that I'd like to retrieve


I have a data table that I'd like to retrieve records from on "years" that fall within the minimum and maximum year range by category on the Parameters table. For example, based upon my parameters, the data highlighted in yellow below is the data that would be retrieved.


I appreciate any recommendations as to what would be the most efficient way to execute this. Ideally, I'd prefer to do this through SQL, but if this can't be achieved in a single query, I'm open to running a VBA function or whatever you think is best.

Thanks in advance for any suggestions!

Dear Experts:


I would like to check if the 'Wss' Worksheet's used range has data, i.e. the worksheet is blank or not ...

The macro however should only check the visible cells, because there are thousands of hidden rows which contain data.  

If all of the visible cells are blank (again, the invisible / hidden cells are not to be considered) the macro is to say so and the macro is to exit

Help is much appreciated. Thank you very much in advance.

Regards, Andreas



Sub Check_For_Blank_Cells_in_Visible_Cells_Only

Dim Wss As Worksheet
Dim i As Integer

Set Wss = ThisWorkbook.Worksheets("Customized_Choice")


End Sub

This question relates to previous question...
https://www.experts-exchange.com/questions/29139086/VB-code-to-modify-records-associated-with-another.html

I tried to add a further comment that I hoped would re-open it but it does not seem to do that - sorry.

So my followup question is ......
The solution code given in the original post works fine and sets the required flag in the record.  However, I do also need to write some text into a field in the record   (CommentsNotSent) taken from a field on the form (CancellationText).

My code currently looks like this (and works fine) ....
With rs
        If .RecordCount <> 0 Then
            Do While Not .EOF
                If vbYes = MsgBox("Would you like to write off this invoice ...." & vbCrLf & vbCrLf _
                & "Number - " & ![Invoice ID] & vbCrLf _
                & "Date - " & ![InvoiceDate] & vbCrLf _
                & "Type - " & ![InvoiceType] & vbCrLf _
                & "Description - " & ![Item_1_Description] & vbCrLf _
                & "Amount - £" & ![Total_Amount], vbQuestion Or vbYesNo, "Write off invoice") Then
                    .Edit
                    ![WrittenOff] = True
                    ![CommentsNotSent] =  [CancellationText]
                    .Update
                End If
                .MoveNext
            Loop
        End If
    End With

However, the problem is that the field CommentsNotSent may already contain some text and the new text needs to be …

Dear Experts:

I got an .xlsm file which only should be saved as .xlsm file by the user.

Is it possible to restrict the user to just save it as .xlsm-file and not as an .xls or .xlsx or other file type? And If yes, how would this VBA code look like?

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

Dear Experts:

I got a form with Active X Control boxes.

In the below code only Range("F23") is checked whether it contains the boolean value 'False'.

I would like to expand the IF statement so that Range "F23" to Range "F85" is included, i.e. the IF statement is true
 if all cells in that range ("F23" to "F85") have the boolean value of 'False'.

I do not want to add more than 60 'And' formulas to my vba Code. I am sure there is a quicker and shorter way.

Help is very much appreciated. Thank you very much in advance.

Regards, Andreas

Sub Check_for_value_in_a_range()

If ActiveSheet.OLEObjects("chbx_GenSurg").Object.Value = True And ActiveSheet.Range("F23").Value = False Then
ActiveSheet.OLEObjects("chbx_GenSurg").Object.Value = False

End If

End Sub

Select all Open in new window

Dear Experts:

I got a form with Active X Control boxes.

In the below code only Range("F23") is checked whether it contains the boolean value 'False'.

I would like to expand the IF statement so that Range "F23" to Range "F30" is included, i.e. the IF statement is true
 if any number of Cells (1 to 8) in the range "F23" to "F30" has the boolean value of 'False'.

Help is very much appreciated. Thank you very much in advance.

Regards, Andreas

Sub Check_for_value_in_a_range()

If ActiveSheet.OLEObjects("chbx_GenSurg").Object.Value = True And ActiveSheet.Range("F23").Value = False Then
ActiveSheet.OLEObjects("chbx_GenSurg").Object.Value = False

End If

End Sub

Select all Open in new window

calculate days and time between two steps; in the attached file i need to concatenate  number and name and calculate difference between each step; if the step is repeating consider the latest or last updated one

Hello,

I am looking for a procedure in order to  
Open file attached located at: Application.StartupPath
If the file doesn’t exist exit sub with the following message “File: File.name doesn’t exist”
Else continue and transfer sheet “1-“ to active workbook and rename it with current date: YYYYMMDDMMSS. Continue procedure with the following message “Do you want to remove the various sheets except sheet already transferred. If Yes remove them else keep them.

If you have questions, please contact me.
1-Table-template.xlsx

I'm using the following code to download files from the internet and save to a location on my computer. I need to close the current browser window after the download (not the application). Thanks

Dim myURL As String

myURL = "https://portal.kkk.kkknet/wps/portal/unauthportal/home/"


Dim HttpReq As Object
Set HttpReq = CreateObject("Microsoft.XMLHTTP")
HttpReq.Open "GET", myURL, False, "zzzz", "zzzz"
HttpReq.send

ShellEx "https://portal.kkk.kkknet/ibmcognos/bi/?pathRef=.public_folders%2FMAS%2FALJ+HQ+Ad+Hoc+Reports%2FPerformance+Goals%FFY19+Aged+Case+Goal+List&format=spreadsheetML&Download=true&prompt=true"


myURL = HttpReq.responseBody
If HttpReq.Status = 200 Then
    Set oStrm = CreateObject("ADODB.Stream")
    oStrm.Open
    oStrm.Type = 1
    oStrm.Write HttpReq.responseBody
    oStrm.SaveToFile CurrentProject.Path & "\" & "FY19 Aged Case Goal List.xlsx", 2 ' 1 = no overwrite, 2 = overwrite
    oStrm.Close


    
End If

Select all Open in new window

Hi Gurus,

Is there a way i can timers set in excel
for eg :

1.  i will have two columns ie start date and end date , i want in third column to tell me with the current date on how many days left
2.so if i reach threshold lets say ' three days left' the cell should change color to red or any other color .

Reason is to monitor the tasks assigned to my colleagues and to follow up priorities based on nearest deadline dates


Thanks,
Sid

In my excel spreadsheet 2016 I got this Visual Basic Error when I am trying to pass  date parameters to my store procedure.
Not sure why this is happening.


Run Time Error ‘9’
Subscript out of Range
Private Sub CommandButton1_Click()

Dim SellStartDate As Date  'Declare the SellStartDate as Date
Dim SellEndDate As Date    'Declare the SellEndDate as Date

SellStartDate = Sheets("Sheet1").Range("B3").Value   'Pass value from cell B3 to SellStartDate variable
SellEndDate = Sheets("Sheet1").Range("B4").Value     'Pass value from cell B4 to SellEndDate variable


With ActiveWorkbook.Connections("SP_Connection2")<<<<<< Error line here - solved
 .OLEDBConnection.CommandText = "EXEC Datawarehouse.dbo.sp_Test_Data '" & SellStartDate & "','" & SellEndDate & "'"
 .Refresh <<<< Run Time Error '1004'
End With

End Sub
I solved it it was wrong speling for : "SP_Connction2" vs "SP_Connection_2".
Now I passed it and got  this error :
Run-Time error '1004'
Exception has been thrown by target of an invocation

I have a search form that looks for the first instance of a record, such as last name and opens the corresponding form, employee.

But what if there are more than one person with the same last name, that is when I would like for the next and previous record button to become available.

I know there is a way of doing this,
Something along these lines, just not sure of the syntax.

If Record count > 1 then
me.previous.enabled = true
me.next.enabled = true

John

End If

Hi ,
i would like to know for my attached excel , is there a way i can make it menu based .

1 . Serial Number i would like to go in sequence starts from 1
2. Brand , i should select from pull down ( not in excel ) but in menu format
3. Multi type selection i need to get it from menu .

if you can give me ideas, it would be great for me start of with the project .

Cheers,
Sid
Test1-Menu-VBA.xlsx