Macro requirement

Topic

New Reply

Hello,

I have a report in Excel 2007 format comprising 5 columns, namely, Title, Object ID, Issue No., Version, and External ID, which is filled by each member of my team.

My requirement is to create a Macro (that is compatible with 2003 and 2007), where when a user update a column, “Object ID”; he should be enforced to fill the columns, “Issue No.” and “Version” as well and should be restricted from saving or closing the document unless the mentioned columns are filled.

For reference, I am attaching the report I am working on.

Thanks in advance.

Regards,
Gurpreet Kaur

Reply | Quote

Replies

I understand preventing saving and closing the document if “Issue No.” and “Version” are empty.

What do you mean by “enforced to fill the columns, “Issue No.” and “Version”?” what exactly do you want the code to do after someone enters something in ObjectID?

Steve

Reply | Quote

Thanks for you reply, Steve!!

By enforced, I meant that as and when the content of “ObjectID” field is changed, the person should be forced to also update the “Issue No.” and “Version” fields without which should not be allowed to save and close the Excel file.

Reply | Quote

If they truly are “forced to also update the “Issue No.” and “Version” fields” then prevention of saving and closing without them being done is impossible.

But the question remains “what exactly do you want the code to do after someone enters something in ObjectID?” How do you envision code “forcing” them to do something. For example, imagine the user makes an entry in B2. After the entry is made, what do you want excel/vba to do exactly?

Steve

Reply | Quote

Sorry for not being clear in my previous posts.

My requirement is when the ObjectID is updated, the Version and Issue no. columns corresponding to the ObjectID should ALSO be updated.

Since we are already using this report, I observed people forget to update the version and Issue no., that is why to ensure that both of these are also updated I thought if there can be a macro for this.

For example:
If the user changes the value in B2, he should also change the corresponding C2 and D2 cells.

P.S: There could be a scenario where objectID does not change at all but if it gets changed, the other two columns that is, issue no. and version should also be changed.

Reply | Quote

Hello Steve,

I managed to put the condition, that is, when Column B is filled the application prompts to enter the value in corresponding C and D cells.

Now I want that whenever the Column B value changes, the prompt for filling the issue no. and version should again be prompted.

In current scenario, it works only when I deleted the content but if I edit the value by double clicking the cell, the prompt does not come.

Can you help on this, please ??

Thanks,
gurpreet

Reply | Quote

I understand the requirement. But it seems to be an issue with training your people. We can create code to fill in the values (if you define what numbers you want the code to fill in) or even post a note that it must be filled. It can be checked when they close and save.

But how do you want to enforce it after they enter something in B and before they try to save and/or close. If they save or try to close and do not fill values in c&D do you want the non-compliant values in B erased or do you want to do something else?

The question is defining enforcement…

Steve

Reply | Quote

As it is written, when an entry in col B is edited the code notes the change in B, but since C&D are NOT blank (the requirements are fulfilled), it does not prompt. If you want to always be prompted,clear the contents of C&D in the row whenever a change is made in Col B.

Steve

Reply | Quote

how can i detect if something has changed in the range of cells ?

Reply | Quote

The “target” in the worksheet_change event is the range of cells that has been changed. That is how the “intersect” part of the code functions.

That is a very general answer to a very general question. Are you asking for something in particular?
Steve

Reply | Quote

Sorry for asking an obvious question, as I am a novice to Excel VBA programming.
I want that whenever anyone updates any cell of column B, the complete row should be highlighted with yellow color.

That’s why asking if we can scan the complete column to find out the particular cell that got updated.

Would request if you can provide the code as well. I have googled a lot but nothing is coming out as per my requirement.

Thanks.

Reply | Quote

Gurpreet,

This code will color the entire row when ever a cell in Col B is changed. The code has to be placed in the Sheet where you want to use it, in this case Sheet1, see graphic.

Code:

Option Explicit

Sub Worksheet_Change(ByVal Target As Range)

    If Application.Intersect(Range(“B:B”), Target) Is Nothing Then
      ‘*** Do Nothing not interested ***
    Else
      With Target.EntireRow.Interior
          .ColorIndex = 3
          .Pattern = xlSolid
      End With
    End If
    
End Sub

You’ll probably need code to execute upon saving or reloading the workbook to clear the highlighting.:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thank you so much RG!!

This is what I was looking for Thanks a tonne !!
This works perfectly fine, however, I observed that it highlights even when the cell is simply double clicked, when no text is either entered or removed. Can Excel in any way detect this as well, whether its just a double click or actually clicked for editing purpose ?

Reply | Quote

I prefer the construction:
If Not Application.Intersect(Range(“B:B”), Target) Is Nothing Then
With Target.EntireRow.Interior

So it does not need the ELSE part…

Steve

Reply | Quote

Steve,

Good Point!:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

This will stop dbl-clicking from going into edit mode for column B:

Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Not Application.Intersect(Range("B:B"), Target) Is Nothing Then
    Cancel = True
  End If
End Sub

If you never want the dbl-click to put the cells into edit mode you can do this for a sheet with the code:

Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Cancel = True
End Sub

Steve

Reply | Quote