Posts by DANDERSON

I want to hide row 10 if A10="0", and unhide if the value changes.

I have come up with the following, but I cant seem to figure out how to watch multiple rows.

This works the way I want it for one row.

I want rows A10-A159 watched.

Thanks again,
Dan

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("$A$10") = "0" Then
Range("$A$10").EntireRow.Hidden = True
Else
Range("$A$10").EntireRow.Hidden = False
End If

End Sub

Awesome! That's it!

Thank you very much. That's exactly the outcome I was looking for. I thought I was close, but I like your solution better, and I learned something new.
Thank you very much Richie(UK).

Thanks for the Help Richie,
How would they be compared to the cells for verification on the Check sheet as shown in the Cases?

All the code is posted, that is in the file, just enter values of your own to test on rows given.

Thanks, perplexed....

I am including a sample file that can illustrate what I have working so far.

I basically want to watch multiple cells, and if their values change,(by comparing them to the Check sheet), code will execute.

I want to watch cells on (sheet Working Data, rows 24 & 25, cells B-BJ.) and compare them to (sheet Check, rows 24 & 25, cells B-BJ).

It works great for the sigle test cell B2, but I now want to watch multiple cells.

File Attached:

Thanks for any help, sugesstions.
Dan

Sheet2 Working Data:

Private Sub Worksheet_Calculate()
   Worksheet_Change Range("B24")
End Sub


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim checkStr As String


checkStr = Sheets("Check").Range("B24")
'   Level 1:  Set up the event to watch a single cell.
    If Target.Address = Range("B24").Address Then


'       Level 2: Perform some action based on the value of the watched cell.
        Select Case Target.Value
            Case Is <> checkStr
                Target.Font.ColorIndex = 2
                    With Target.Interior
                        .ColorIndex = 3
                        .Pattern = xlSolid
                    End With
                Range("A3:A6").Select
                Selection.Font.ColorIndex = 2
                    With Selection.Interior
                        .ColorIndex = 3
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        Range("B24").Select
                    End With
            Case Is = checkStr
            Target.Font.ColorIndex = 5
                With Target.Interior
                        .ColorIndex = xlColorIndexNone
                        .Pattern = xlPatternNone
                    End With
        End Select
    End If
End Sub




Module1:
Sub GoalSeekQuick2()


                    Sheets("working data").Select
                    Range("A3:A6").Select
                    Selection.Font.ColorIndex = 10
                    With Selection.Interior
                        .ColorIndex = 10
                        .Pattern = xlSolid
                    End With
                    Rows("24:25").Select
                    Selection.Interior.ColorIndex = xlNone
                    Selection.Font.ColorIndex = 5
                    Selection.Copy
                    Sheets("Check").Select
                    Range("A24").Select
                    ActiveSheet.Paste
                    Range("A1").Select
                    Sheets("working data").Select
                    ActiveWindow.SmallScroll Down:=-1
                    Range("A1").Select
                    Application.CutCopyMode = False
                    Range("B24").Select
                
                    MsgBox "test macro executed"
End Sub

Hmm, I tried the conditional formatting, but I am not sure it does what I want. The Number will change constantly, so I just want to flag the cell each time it changes. I am not understanding how to setup a conditional format to do just that, with equal to, less than, etc, because I need a reference cell, and I just want the same cell to be flagged when it changes.....I do not understand, but appreciate the help.

Basically, I want to creat some notification to the user when a specific set of cells change. I was thinking that I possibly need to name the cells, then create some sort of event when thier values change, and pop up a notification box to the user stating they need to run a specific macro.

Any ideas would be appreciated.

I was wondering how hard it would be to chnge the color of the cell when the value changes as well.

Thankd again all.

Great!

Thank you for the help. Learn something everyday.

The value in A! is a cell.
I found a work around using LEFT/Right/CONCATENATE, but I would hope there is a better way.

I want to copy the displayed vvalue to another application, but when I copy the selected cell, I can only get the actual value.

IE:

A1=28,117

What I get when I copy is 28116.7805

Thanks for any help.

Thats it! Thank you very much!

Ceiling.....Huh, never came acrossed it, thank you.

I have numbers in my data that need to be rounded up to the neares 50.

I know how to use roundup to nearest 10, 100, etc..but that wont work with 50.

IE: 20,255 = 20,300
20,245 = 20,250

Thanks for any suggestions.

Thank you very much, I will work on it!

I have varying number of sheets in a workbook.

All new sheets (0420xxxx) are created and sorted through vb between sheets named:
0000a000 & 9999z999

I want to be able to copy cells A2:B2 on all sheets between to Summary Sheets.

(Existing Sheet) Summary
I wish to copy cells A2:B2 on all sheets between 0000a000 & 9999z999 to multiple rows as shown here:

[A3] B1 [B3] 37
[A4] B2 [B4] 3
[A5] B3 [B5] 40

(Existing Sheet in position 7) 0000a000 (Blank)

(New Sheet in position 0420XXXX
[A2]B1
[B2]37

(New Sheet in position 9) 0421XXXX
[A2]B2
[B2]3

(New Sheet in position etc.) 0422XXXX
[A2]B3
[B2]40

(Existing Sheet in last position(currently 11 but changes)) 9999z999 (Blank)

Thank you for any help!

I just read about late binding. Currently, I just opened up the .dvb file on the Office 2000
machine, and changed the Reference Object Libraries to the Office 9, and
Outlook 10, which match their machine, and Saveas a 2K.dvb version. It works
for now, but in the long term, I will want to compile the code. I am
researching it now.
I think this is in the right direction....Anybody have any other ideas?

I created a vba app accross ACAD/EXCEl/OUTLOOK, what do I do with multiple
Office Environments?

Half of the drafters have Office 2000, and half Office 2003. The object
libraries obviously don't match.

(I am new at this...)

We are all on ACAD2k5. The app was developed in O2K3. I am vaguly aware of
compiling the code, but I know that there is a lot of work needed in order
to create an.dll or .exe. Am I correct that much of the code has to change
from .dvb to create a compiled .dll or .exe?

I do have Visual Studio 6 in the office.

Some problems I have already encoutered in the test I ran quickly to cimpile
is:

ThisDrawing. (errors)

End (errors)

Thats just the beginning.

Any advice is appreciated.

Thats it! Thank you very much!

Thats Exactly what I needed. Awesome. That was that last step I needed to figure out, and you made it so simple. Plus I learned a new copy method with out the clipboard. Thanks.

Ok, I have added the Private sub, how to I make it work with the following code in my module?

itemNo = Application.InputBox("Enter Item Number: ", 2)

Cells.Find(What:=itemNo, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

'select specs rows operation here

Selection.Copy
Range("A60").Select
ActiveSheet.Paste

I am trying to figured out how to select rows automatically from an active cell(A1), blank (A2), blank(A3) blank(Etc). There will be data in column B-?. Colum A will have one item number, and specifications in colum B-?, and the rows after until a new item number is given.

I have (for example):

A B C
1 234 abc abc
2 abc abc
3 abc abc
4 abc abc
5 abc abc
6 abc abc
7 234 abc abc
8 abc abc
9 abc abc
10 234 abc abc
11 abc abc
12 abc abc
13 abc abc
14 abc abc

I need to figure out the VB when A1 is selected, Rows 1-6 are selected.
Thank for your help. I have learned a lot from here, but did not see this type off selection set when I searched.

For clarification, and example has been posted.

The attached shows the number of rows changes between entries, and there is not blank line between them.

Hmm, gives me sommethng to go on. I don't need to print, I just want to delete empty data in rows. When I manually delete rows 2-10000, the file size drps back down to like 80K.