Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

Thel: Apr 28, 2010

I've been asked to put together a worksheet to go on sharepoint so the building/grounds/property guys can track job requests.

So, they want:
- Password-protected sheet (done, but off while I struggle with the rest of it)
- Three user-editable columns (done):
-> location, dropdown select from a list
-> staff name
-> description of job/issue

The bits I'm struggling with:
- Timestamp in a cell when a new job is entered (ie. the location select is column B, they want to automatically fill in the appropriate row in column A, when the location is selected, with the current time - no overwriting)
- Lock the user-editable cells once they have data in them.

The code I have so far:

code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  With Target
    If .Count > 1 Then
      Debug.Print "Exiting Sub - Count > 1"
      Exit Sub
    End If
    If Not Intersect(Range("B2:B5000"), .Cells) Is Nothing Then
      Debug.Print "Intersection with column B, proceeding"
      Application.EnableEvents = False
      If Not IsEmpty(.Value) Then
        Debug.Print "Column B not empty, proceeding"
        With .Offset(0, -1)
          If IsEmpty(.Value) Then
            Debug.Print "Column A empty, proceeding"
            'ActiveSheet.Unprotect
            .NumberFormat = "dd/mm/yyyy"
            .Value = Now
            'ActiveSheet.Protect
          Else
            Debug.Print "Column A not empty, aborting"
          End If
        End With
      Else
        Debug.Print "Column B empty, aborting"
      End If
      Application.EnableEvents = True
    Else: Debug.Print "Herp"
    End If
  End With
  If Target.Cells.Count > 1 Then Exit Sub
  If Not Intersect(Target, Range("B3:D5000")) Is Nothing Then 'set your range here
    'ActiveSheet.Unprotect
    Target.Locked = True
    'ActiveSheet.Protect
  End If
End Sub

Related, are there any useful resources for debugging VBA in excel?

e: Discovered Debug.Print. Scattered a few of those through this chunk of code, now it's doing exactly what I want it to do. :what:

Excel.

Thel fucked around with this message at 00:42 on May 11, 2011

# ¿ May 10, 2011 22:27

Adbot: ADBOT LOVES YOU

# ¿ Apr 28, 2024 09:43

Thel: Apr 28, 2010

G-Dub posted:

I don't mean to sound cheeky, but do you know about breakpoints?

Yes, I couldn't get them to work for a while (I don't know why).

e: Given that I just spent the last ten minutes being a blithering idiot, it's entirely possible that when breakpoints weren't working I'd managed to completely miss Excel's macro security warning. :hurr:

Also, they don't work very well for Worksheet_Open() :v:

Thel fucked around with this message at 00:11 on May 12, 2011

# ¿ May 11, 2011 22:41

Thel: Apr 28, 2010

Finally got everything working the way I want.

Except the loving macro warning pops up every time I open it. This is a sheet that will eventually be going on the company sharepoint server (so like http://companyname-sharepoint/foldername/Maintenance Spreadsheet.xls). Is there any way I can set a URI as a trusted location in Excel? Signing isn't going to work since the spreadsheet will be constantly changing.

Something that can be pushed to all users by GPO would be awesome, but if they have to set it up themselves that's fine (I can write a guide for that :j:

).

e: I don't know what a "loving macro wartning" is, but I want no part of it. :stare:

# ¿ May 12, 2011 05:01

Thel: Apr 28, 2010

Swink posted:

Using the Office 2010 GPO admin templates, you can specify trusted directories, so all files within that directory will auto-enable macros. Not sure if you can use it to specify sharepoint location though.

Edit - We only have policies in place for Word, but I assume Excel has similar options.
User config > admin Templates > Word 2010 > Word Options > Security > Trust Centre > Trusted Locations

e2 - On re-reading your post, I think i may have just told you a heap of stuff you already know.

GPOs are a black art to me, I'm a DBA (at least if you look at my job description). All I know is they make stuff work, and our sysadmin manages to break everything every time he touches them. :laugh:

(rookie sysadmin, before this he was a cable jockey for an ISP. Smart kid, but no prior knowledge. Augh.)

Anyway, we're running Office 2007 on a mixed domain (2003 functional level, one 2003 DC and one 2008R2 DC), so I just grab the 2007 Admin templates, throw them on our R2 DC and write up an all-user GPO to add the trusted locations for Excel?

(In theory we're going to be upgrading everything to 2008R2 this weekend. Explosions inc.)

# ¿ May 15, 2011 22:25

Thel: Apr 28, 2010

Hm.

I have a spreadsheet with a worksheet for each month (May 2011, April 2011, March 2011 blah blah blah) and in each worksheet is a line of data for each location we have. I need to find a way to view by location rather than by month. Would I be better off importing the data into SQL and rearranging that way?

i.e. currently:
April:

pre:

Location	Car WOFs	Fire drill		Smoke alarms  
Street #1	Due May		Done 15/1, next 15/7	Hurf durf
Street #2	Due Jun		Done 4/2, next 4/8	Smurf blurf

May:

pre:

Location	Car WOFs	Fire drill		Smoke alarms  
Street #1	Done 2/5	Done 15/1, next 15/7	Hurf durf
Street #2	Due Jun		Done 4/2, next 4/8	Smurf blurf

What I need:

Street #1:

pre:

Month		Car WOFs	Fire drill		Smoke alarms
April		Due May		Done 15/1, next 15/7	Hurf durf
May		Done 2/5	Done 15/1, next 15/7	Hurf durf

Street #2:

pre:

Month		Car WOFs	Fire drill		Smoke alarms
April		Due Jun		Done 4/2, next 4/8	Smurf blurf
May		Due Jun		Done 4/2, next 4/8	Smurf blurf

# ¿ May 26, 2011 01:20

Thel: Apr 28, 2010

Lt Moose posted:

How can I make the columns of my bar graph wider? I already have the gap width set to 0%, but there still is tons of space between each value.
Here is what it currently looks like:

I've run into this before, the problem is the date fields. Create a second column with a text format (using =TEXT(<cell>,"m/d/yy") and use that column as your axis.

(Excel is very helpful and goes "Okay, so you have a value then, and another value seven days later, so that must mean six days of no data" and draws it up like that.)

# ¿ Jul 10, 2011 20:55

Adbot: ADBOT LOVES YOU

# ¿ Apr 28, 2024 09:43

Thel: Apr 28, 2010

Excel 2007: So, I want to be able to put a horizontal line (i.e. a border) between data from different clients - automatically, e.g.

What I have:

code:

Client Number     Other Stuff       More Other Stuff
11111111            Stuff             Stuff
11111111            Stuff             Stuff
11111111            Stuff             Stuff
22222222            Stuff             Stuff
22222222            Stuff             Stuff
33333333            Stuff             Stuff
33333333            Stuff             Stuff
33333333            Stuff             Stuff
33333333            Stuff             Stuff

What I want: (pretend the line of ===== is a border - it shouldn't take up it's own cell)

code:

Client Number     Other Stuff       More Other Stuff
11111111            Stuff             Stuff
11111111            Stuff             Stuff
11111111            Stuff             Stuff
======================================================
22222222            Stuff             Stuff
22222222            Stuff             Stuff
======================================================
33333333            Stuff             Stuff
33333333            Stuff             Stuff
33333333            Stuff             Stuff
33333333            Stuff             Stuff

I've actually managed to do this, but it was a terrible hack:
- Set up a conditional formula on cell A3 to put a border on the top of the cell if the value in A2 doesn't match the one in A3, making sure to take out the '$' on the row value (so $A3 instead of $A$3) so the cell references will move.
- Copy the cell, then 'paste special - formats only' into the next cell across (B3).
- Copy A3-B3, 'paste special - formats only' into C3.
- Repeat that until the conditional format is across all the rows you need it.

- Copy 3:3, 'paste special - formats only' into row 4.
- Copy 3:4 (rows 3 & 4), 'paste special - formats only' into row 5.
- Repeat that until Excel crashes with an out-of-memory error. :v:

fe: Wow I'm retarded. It turns out I can do this just by selecting A3:<lower right corner of data block>, then conditional formatting -> new rule -> use a formula [bottom option] -> formula: =$A2<>$A3 and set custom format: border top.

Once again, Excel proves to be significantly smarter than I am. :derp:

# ¿ Aug 12, 2011 06:49

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread