Posts by djagxk8

Re: run macro from data validation drop down error

Works like a champ! Thank you!!!

I have two macros that work great independently, but for some reason I can't get them to work together.

Macro 1:
On worksheet 1, I have a data validation drop down list in cell F2. Any time the cell is selected (regardless of content), I want it to run the same macro (the macro is named "Size"). The code I am using for that, and where I suspect the problem is, is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Set KeyCells = Range("F2")
    Call Size
  End Sub

Macro 2:
This macro is the one that I have the code above calling. It works great when I call it manually, or attach it to a button, but for some reason I always get error messages when I run it from the macro above.

Sub Size()
ActiveSheet.Unprotect
Set myRange = ActiveCell
    Range("B10").Select
    Selection.Copy
    Range("P10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Rows("10:10").EntireRow.AutoFit
    Range("B20").Select
    Selection.Copy
    Range("P20").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Rows("20:20").EntireRow.AutoFit
    Range("B24").Select
    Selection.Copy
    Range("P24").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Rows("24:24").EntireRow.AutoFit
    Range("B26").Select
    Selection.Copy
    Range("P26").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Rows("26:26").EntireRow.AutoFit
    Range("B30").Select
    Selection.Copy
    Range("P30").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Rows("30:30").EntireRow.AutoFit
    Range("J30").Select
    Selection.Clear
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True
myRange.Select
End Sub

The error says: "the cell or chart you're trying to change is on a protected sheet. To make a change, unprotect the sheet. You might be requested to enter a password."

However, when I unprotect the sheet, and delete all the .protect / .unprotect tags from the code - the macro runs, but right at the end it freezes excel. I have no idea what the solution is - any help would be greatly appreciated.

Thanks,
D

Re: Hide/Unhide rows in range if cell value = criteria

Perfect, thank you! I appreciate all your help.

Re: Hide/Unhide rows in range if cell value = criteria

That worked like a champ, thanks! Is there a way protect the sheet without interfering with the code - or do I just need to keep it unlocked?

Re: Hide/Unhide rows in range if cell value = criteria

Here's an excerpt of the sheet. All the red/green cells in the grid part is just dummy data, but everything else is functioning as it should.forum.ozgrid.com/index.php?attachment/68861/

Not sure if it attached, let me know if not.

Re: Hide/Unhide rows in range if cell value = criteria

The whole worksheet is unlocked at the moment, but I intend to lock it once I'm done.

The values in the range B7:B75 are populated by a reference to another sheet. For example, B7 of my range says =Reference!A3. On the Reference sheet, cell A3 is determined by an index match function that is pulling from that reference sheet.

Re: Hide/Unhide rows in range if cell value = criteria

Please excuse my ignorance- yes I put the cursor in the code and hit F5.

If by installing it in the worksheet module you mean right clicking the worksheet and selecting view code, and pasting the code under the code I already had there, then that's what I did.

Perhaps they don't like running together? Not familiar with rules surrounding event codes, or if there's an order they need to be placed in.

When I took the previous (hide columns) code out, and put in your code for the rows by itself, I got a run-time error '2147417848 (80010108)': Method 'Hidden' of object 'Range' failed.

Re: Hide/Unhide rows in range if cell value = criteria

Thanks for the reply! Not sure what's going on, but the code didn't do anything when I changed things on the sheet, but when I ran it manually - the whole sheet locked up and I had to restart excel.

Happy (almost) Friday!

The way this sheet is set up, each cell in Range B7:B75 contains a formula that updates to display a "0, 1, or 2" depending on information entered elsewhere on the sheet. I'm trying to write the macro so that whenever a cell in range B7:B75 =0, that particular row will hide. I also need it to unhide when it no longer = 0. I need this to update in real time whenever those values change.

I already have a macro running on this sheet to hide columns in a similar manner (which works perfectly), but I can't figure out the hide/unhide rows part. Not sure if I should try to add it to my existing macro, or if I should create a second one to handle the rows... Here's the macro that works for the columns:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("B1").Value = 4 Then
        Columns("H:N").EntireColumn.Hidden = True
    Else
        Columns("H:N").EntireColumn.Hidden = False
    End If
End Sub

Any help on this would be greatly appreciated!

Thanks,
DT

Re: Hide shape if cell value equals specific amount

Hi Max,

That was precisely the problem. I just looked up my shape names and changed it in the code and now it works like a champ.

Thanks for the help!
DT

Re: Hide shape if cell value equals specific amount

You know, I have a another workbook that does this function but I didn't make it, so I'm not sure whether or not the code can be adapted to this workbook.

Private Sub Worksheet_Calculate()
 Application.EnableEvents = False
    On Error Resume Next
    If Sheets("Sales LY (Monthly)").Range("AU2").Value <> PrevVal Then
        Call unhidemail 'not sure what this line is... perhaps the name of the macro that unhides the button to mail the form out?
        PrevVal = Sheets("Sales LY (Monthly)").Range("AU2").Value
    End If
        
    On Error Resume Next
    Application.EnableEvents = True
   End Sub

I changed parts to reference my sheet, but I'm not sure what the "Call unhidemail" part means, or what PreVal means. I'm thinking I should change the line

If Sheets("Sales LY (Monthly)").Range("AU2").Value <> PrevVal Then

to say

If Sheets("Sales LY (Monthly)").Range("AU2").Value = 1 Then

but I'm still not getting anywhere.

Thanks,
DT

Re: Hide shape if cell value equals specific amount

Hi Max,

In the tab with the button (which is also the tab the value in cell AU2), I put the following code:

Private Sub CommandButton1_Click()




'Execute macro here




ActiveSheets.Shapes("Please press to clear data from 5th Week").Visible = False




End Sub

And in the reference tab, I put the code:

Private Sub Worksheet_Change(ByVal Target As Range)




Dim ws As Worksheet
Dim ButtonName As String




Set ws = Sheets("Sales LY (Monthly)") 'Change this to the sheet which the shape is located in
ButtonName = "Please press to clear data from 5th Week" 'Change this to the name of the shape








If Target.Address = Range("W14").Address And ws.Range("AU2") = 1 Then
    ws.Shapes(ButtonName).Visible = True
End If








End Sub

But, it's not doing anything. Did I do all the steps correctly?

Thanks,
DT

Re: Hide shape if cell value equals specific amount

Hi Max,

The sheet with the button is called Sales LY (Monthly). Right now that is the same sheet with AU2 on it. However, if it can come straight off my reference sheet and not have to refer back, that would be nice too.

The name of my reference sheet is Reference, and the cell that AU was referencing is W14.

Thank you,
DT

Re: Hide shape if cell value equals specific amount

Hi Max,

Thank you for the help! The change of value in AU2 is indeed running from a formula. It is coming from my reference tab in cell W14 (Reference!W14). This is where the calculation is taking place to determine whether or not the button should be hidden.

Thank you!
DT

Good afternoon,

I have a 3 shapes on my worksheet functioning as buttons, and I need one of those three to stay invisible unless certain criteria are met (specifically, cell AU2 equaling 1). Then, once the it becomes visible, I need it to disappear again after it is clicked (since clicking it will run another macro to zero out cell AU2). I hope this is enough information to help. Thank you in advance for your time!

DT

Re: Rename select tabs from cell references

Solved! I didn't know you could refer to the array by codename rather than worksheet name.

Option Explicit
Sub renameregion()
     
    Dim varMySheet As Variant
     
    Application.ScreenUpdating = False
       
    For Each varMySheet In Array(Sheet6.Name, Sheet7.Name, Sheet8.Name, Sheet9.Name)
        On Error Resume Next
        Sheets(CStr(varMySheet)).Name = Sheets(CStr(varMySheet)).Range("A1")
        If Err.Number <> 0 Then
            MsgBox "could not rename sheets", vbExclamation
            Err.Clear
        End If
        On Error GoTo 0
    Next varMySheet
     
    Application.ScreenUpdating = True
     
End Sub

Re: Rename select tabs from cell references

Okay, I don't know the solution, but I think I figured out the problem I'm having.

The error is happening because my sheets aren't originally named "Sheet6", and so on. Is there a way to change the sheet names regardless of what they're original names are?

Thanks again!
DT

Re: Rename select tabs from cell references

Thanks Robert,

For some reason it's just giving me the error message. Could it be because the cell I have it referencing for the name is populated by a formula?

Here's what I have so far:

Option Explicit
Sub renameregion()
     
    Dim varMySheet As Variant
     
    Application.ScreenUpdating = False
     
    For Each varMySheet In Array("Sheet6", "Sheet7", "Sheet8", "Sheet9")
        On Error Resume Next
        Sheets(CStr(varMySheet)).Name = Sheets(CStr(varMySheet)).Range("J1")
        If Err.Number <> 0 Then
            MsgBox "Sheet """ & varMySheet & """ could not be renamed by the suggested name in cell J1.", vbExclamation
            Err.Clear
        End If
        On Error GoTo 0
    Next varMySheet
     
    Application.ScreenUpdating = True
     
End Sub

Thanks,
DT

Good afternoon,

I am totally stuck on this one. I have a document with 9 tabs in it, and I only want to rename tabs 4, 5, 6, and 7 from cell E3 in each of those tabs. Everything I try either renames all the tabs, or only tab 1. I greatly appreciate any help here!

Thanks,
DT

Re: Run-time error '9' when renaming document

<FACEPALM> I knew it would be something simple simple like that, thank you so much- it works beautifully!