Posts by msjacks

I have a spreadsheet with several rows of data. Would I would like is a pair of buttons on each row where the user could approve/not approve the info on that particular row.
I've tried Option buttons from the Forms toolbar, but you can only choose one option on the entire page, not one per line.

Anyone have any thoughts?

Re: Macro Is Shutting Down Application

Yep. I replaced my code with yours and I ran it and it still shut down. In the same place it seems.

I noticed that you have ".Close SaveChanges:=True" in your code. It still asked me if I wanted to save changes when it tried to close the file (this was before Excel gave me the error).[hr]*[/hr] Auto Merged Post;[dl]*[/dl]I had an Add-in called Hyperion Smart View for Office loaded on my excel. I unchecked the add-in and ran it, and it worked now.
It seems that add in was interfering with the macro.
Thank you for your help.

Everytime I run this macro it shuts down Excel. I get a pop up box saying "Microsoft Excel has encountered a problem and needs to close. We are sorry for the inconvenience."
The thing is, it runs at first and I seem to get this error when it gets to the 'Next' line in the macro.

Is there something in my code that would cause Excel to shut down like this?

Sub Update_template()


For x = 44 To 45


Workbooks("budget macros").Activate
Sheets("Sheet1").Select
Range("B" & x).Select




Dim Location, Thisfile, file_name, folder, file As String
    Location = "X:\East\"
    file_name = Range("F" & x)
    folder = Range("E" & x)
    file = folder & "\" & "Bud08 " & file_name & " Prop Budget.xls"
    Thisfile = Location + file


Workbooks.Open Filename:=Thisfile, UpdateLinks:=False, Editable:=True


Sheets("Revenue").Select
Range("j52").Select
Selection.Interior.ColorIndex = 19


Range("q53").Select
Selection.Interior.ColorIndex = 19
ActiveWorkbook.Save
ActiveWorkbook.Close
Workbooks("budget macros").Activate
Sheets("Sheet1").Select




Next


MsgBox ("done")




End Sub

Re: Macro To Open Workbook With Password

Thank you so much!! This worked, but I have one more question.
The file I am opening is also linked to another file. I do not want to update the links. Anyway to add this into the macro?[hr]*[/hr] Auto Merged Post;[dl]*[/dl]I found the update link info in another thread.

Thank you again for your help.

I have a workbook with the following code:

Sub Workbook_Open()


Sheets("Occ-Rent-Concess Worksheet").Select
ActiveSheet.Unprotect Password:="pass"
ActiveSheet.Select
Cells.Locked = True
ActiveSheet.Protect Password:="pass"


pass:
    UserName = InputBox("Enter Log On Name to Continue                                      (Type 'read' for Read Only Access)")
    
    
    If UserName = "read" Then
    MsgBox "Read Only Access"
    GoTo readonly
    
    Else


    
    If UserName = "maintmac" Then
    Sheets("Occ-Rent-Concess Worksheet").Select
    ActiveSheet.Unprotect Password:="pass"
    Dim iColorMaint As Integer, cMaint As Range
     'figure out here what colorindex to unlock
    iColorMaint = 40
    For Each cMaint In Range("A3:V300") 'adjust as needed
        With cMaint
            .Locked = Not (.Interior.ColorIndex = iColorMaint)
        End With
    Next cMaint
    ActiveSheet.Protect Password:="pass"
    MsgBox "Maintenance Cells (Peach) Unlocked"
Else


    If UserName = "finplan" Then
    Sheets("Occ-Rent-Concess Worksheet").Select
    ActiveSheet.Unprotect Password:="pass"


    
    MsgBox "All unlocked"
    
    Else


EndSub

This code opens a msgbox that asks for a password and then unlocks certain cells based on what the password is.

I have 140 of these workbooks. I need to be able to open them all and change one cell in each file, save it and close. I've gotten the macro written to open, change and save the files, but I am still having to type the 'password' 140 times. Is there a way to bypass it?

Re: Sql Code Converted To Access

Thanks a whole bunch. I'm just not that familiar with writing queries using SQL in access.

Here's what I changed it to. I'm getting an error message Syntax error (missing operator) in query expression ".

SELECT           n.NAMEID
                ,s.SITEID
                ,n.RMPROPID
                ,n.RMBLDGID
                ,n.UNITID
                ,n.RMLEASE
                ,u.CLASSID
                ,c.NMBRBED
                ,n.STATUS
                ,iif(
                   l.VACATE IS NOT NULL,
                     l.VACATE,
                    ISNULL( MAX( ro.RENEXPIRE ), l.EXPIRE ))


FROM             MRI_PROD.dbo.RMLEASE l
INNER JOIN       MRI_PROD.dbo.NAME  n ON
                 n.RMPROPID = l.RMPROPID AND
                 n.RMBLDGID = l.RMBLDGID AND
                 n.UNITID = l.UNITID AND
                 n.RMLEASE = l.RMLEASE AND
                 n.NAMEID = n.NAMEGROUP
INNER JOIN       MRI_PROD.dbo.UNIT u ON
                 n.RMPROPID = u.RMPROPID AND
                 n.RMBLDGID = u.RMBLDGID AND
                 n.UNITID = u.UNITID
INNER JOIN       MRI_PROD.dbo.CLSS c ON
                 u.RMPROPID = c.RMPROPID AND
                 u.CLASSID = c.CLASSID
INNER JOIN       MRI_PROD.dbo.RMPROP p ON 
                 l.RMPROPID = p.RMPROPID
INNER JOIN       MRI_PROD.dbo.SITE s ON
                 s.SITEID = p.MACSITEID
LEFT OUTER JOIN  MRI_PROD.dbo.RMRENOFFER ro ON
                 n.NAMEID = ro.NAMEID AND
                 ro.RENEXPIRE > l.EXPIRE AND
                 ro.SELECTED = "Y" AND
                 ro.CMMTTD = "Y" AND
                 ro.TERM <>"MTM" AND
                 ro.ACTIVE = "Y" 
WHERE             l.OCCDATE <> iif(l.VACATE IS NULL AND n.STATUS NOT IN ( "C","N" ),
                    l.OCCDATE, iif( l.VACATE IS NULL,
                    DATEADD( dd, 1, l.OCCDATE )
, l.VACATE))
GROUP BY         n.NAMEID
                ,l.VACATE
                ,l.EXPIRE
                ,n.RMPROPID
                ,n.RMBLDGID
                ,n.UNITID
                ,n.RMLEASE
                ,s.SITEID
                ,u.CLASSID
                ,n.STATUS
                ,c.NMBRBED
ORDER BY         n.RMPROPID
                ,n.RMBLDGID
                ,n.UNITID
                ,n.RMLEASE

I have been given the code to a SQL Query and I would like to recreate this in access. But obviously access doesn't recognize some of the syntax. Does anyone have an idea on how to update it to work in access?

SELECT           n.NAMEID
                ,s.SITEID
                ,n.RMPROPID
                ,n.RMBLDGID
                ,n.UNITID
                ,n.RMLEASE
                ,u.CLASSID
                ,c.NMBRBED
                ,n.STATUS
                ,CASE
                   WHEN L.VACATE IS NOT NULL THEN
                     l.VACATE
                   ELSE
                    ISNULL( MAX( ro.RENEXPIRE ), l.EXPIRE )
                 END
FROM             MRI_PROD.dbo.RMLEASE l
INNER JOIN       MRI_PROD.dbo.[NAME] n ON
                 n.RMPROPID = l.RMPROPID AND
                 n.RMBLDGID = l.RMBLDGID AND
                 n.UNITID = l.UNITID AND
                 n.RMLEASE = l.RMLEASE AND
                 n.NAMEID = n.NAMEGROUP
INNER JOIN       MRI_PROD.dbo.UNIT u ON
                 n.RMPROPID = u.RMPROPID AND
                 n.RMBLDGID = u.RMBLDGID AND
                 n.UNITID = u.UNITID
INNER JOIN       MRI_PROD.dbo.CLSS c ON
                 u.RMPROPID = c.RMPROPID AND
                 u.CLASSID = c.CLASSID
INNER JOIN       MRI_PROD.dbo.RMPROP p ON 
                 l.RMPROPID = p.RMPROPID
INNER JOIN       MRI_PROD.dbo.SITE s ON
                 s.SITEID = p.MACSITEID
LEFT OUTER JOIN  MRI_PROD.dbo.RMRENOFFER ro ON
                 n.NAMEID = ro.NAMEID AND
                 ro.RENEXPIRE > l.EXPIRE AND
                 ro.SELECTED = 'Y' AND
                 ro.CMMTTD = 'Y' AND
                 ro.TERM <> 'MTM' AND
                 ro.ACTIVE = 'Y' 
WHERE            l.OCCDATE <> CASE 
                  WHEN l.VACATE IS NULL AND n.STATUS NOT IN ( 'C', 'N' ) THEN
                    l.OCCDATE
                  WHEN l.VACATE IS NULL THEN
                    DATEADD( dd, 1, l.OCCDATE )
                  ELSE
                    l.VACATE
                 END

Re: User Prompt In Sql Statement

bump

I am writing a SQL query and everything works, but I'd like to be able to prompt the user to enter the period they want to pull. I'm not sure what the proper syntax is.

SELECT
G.PERIOD,G.ENTITYID,G.DEPARTMENT,P.MACHYP,CASE WHEN G.DEPARTMENT = '@' THEN 
	CASE WHEN P.MACHYP IS NULL THEN G.ENTITYID ELSE 
	P.MACHYP
	END
	ELSE G.DEPARTMENT 
END AS [ENT],G.ACCTNUM,A.ACCTNAME,SUM(G.ACTIVITY),A.TYPE


FROM
GLSUM G
INNER JOIN GACC A  ON G.ACCTNUM = A.ACCTNUM
INNER JOIN RMPROP P ON G.ENTITYID = P.ENTITYID


WHERE
G.BASIS = 'A' AND
G.PERIOD = [ENTER PERIOD]
GROUP BY G.PERIOD, G.ENTITYID, G.DEPARTMENT, P.MACHYP, G.ACCTNUM, A.ACCTNAME, A.TYPE
ORDER BY G.DEPARTMENT

I know the [Enter period] statement isn't right, but does anyone know what the syntax should be?

I have attached a file with an example of what I need.

The data tab show the data has been given to me. I need it in a different format to be able to load it into our system.
The Needs tab shows what format I need.
Basically, I need to convert this table into a flat file, where there is a record for each "X" value only.

I'm not very fluent in creating access queries in SQL. Up to now, I've pretty much only used the Design View:

I have two tables.
One table lists all the residents in June, Another lists all the residents in July.

I would like to be able to create a query that tells me which residents are in June but not in July. (Basically, who moved out during that period)

Re: Max Date Adjustments

The code was given to me by someone else. I copied it directly from the VBA screen.

I don't need them to delete the date.
For example. In cell k1 the user types $80, the macro would populate the date stamp (today's date) in cell Q1. But suppose they didn't mean to put in the $80 and want to undo the $80. Currently this macro prohibits them from being able to use undo.
I was wanting to know if there is a way to still capture the date they change a cell, but without disabling undo.
If there is a different code I should be using - I'm open to any suggestions.3

Maybe if there's a way to have the macro run at the time of saving - it would update the changed cell date stamp on saving. But I don't know how to do that.

I have the following code in my spreadsheet which marks the current date/time whenever a certain column has been updated.

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 11 Then
        Application.EnableEvents = False
       Target.Offset.Offset(0, 6) = Now()
       Application.EnableEvents = True
       Else
       If Target.Column = 10 Then
       Application.EnableEvents = False
       Target.Offset.Offset(0, 8) = Now()
       Application.EnableEvents = True
       End If
        
   End If
End Sub

The problem is that when I have this macro it disables the undo option.
I need to be able to date stamp any changes made but still allow user to undo any changes they make. Is this possible?

Re: Max date in color range

Yes, that worked!! Thanks a bunch!

Ok - now how do I get it to update automatically when I open the file. Right now you have to click on the cell to get it to recalc.

I have a spreadsheet (I've attached an example) that has a date stamp for when column B is updated.

What I would like is to put into cell a1, the max date/time that the yellow cells have.
This is the code that I currently have on Sheet1 to put the time stamp in.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 2 Then
        Application.EnableEvents = False
        Target.Offset.Offset(0, 2) = Now()
        Application.EnableEvents = True
    End If
    
End Sub

I am running a contest for my office and the theme is 'nascar'. Anyway - we'd like to chart the contestants using a racetrack. Is this possible in Excel or any other MS Office tool?

Basically I'd like to show the placement of ten contestants on a circular racetrack. It would be updated weekly.

Re: Send Keys error with Scheduler

Can I bump this? Does anyone know anyway to possibly assist with this?
Or if no one can help with scheduler is there a way excluding send keys to have an access macro open up password protected tables?

Re: data labels on stacked column charts

Ignore this... I found an answer in another thread.

This may be a really easy question - I've tried to find the answer but can't. If I'm overlooking something simple - I apologize.

I am trying to put data labels at the top of a stacked column chart. I want the labels to be the total of the two data series.
I've attached my example.
I want the data labels for "Total Increase" at the top of the columns.

I have set up an access macro to run several queries.
I made a shortcut of this macro and set it with the scheduler to run everyday at a certain time. I need it to run regardless of whether I am in access or not and I am going to eventually save it on the server so it can run whether I am on the computer or not. It works in every aspect except when I have a send keys function as a part of my macro. I'm guessing the send keys doesn't work because when you run a scheduler it doesn't actually open the program on your desktop.

There are passwords on the access tables and I needed the send keys function to type the password in for me.
Is there any way around this? It gives me this error whether I run the scheduled program manually or on the server.
The macro itself does not give me any errors when I run it manually - Just through the scheduler.