Posts by blue

Re: Intersect Method with Worksheet_Change

I am only looking at IntersectRange1 to start with. As soon as I am able to debug that, then I will add the rest of the Intersect Ranges.

And, yes, I understand that more than one cell is getting changed but in trying to debug, I have only changed a single cell but more than one is being triggered.

Stepping thru is difficult when it's a workbook_change event. Or at least I haven't figured out how to step through an event.

Thanks for all your help (and continued help?)

Hi there once again.

I hope that someone can spot my coding error. This same code worked fine last week and I'm not sure what has changed. As you can see by my probably ridiculous code below, I am trying to clear the contents of a range of cells based on the entry in another range of cells.

It is now not only clearing the range of cells that I expect but another random 3 or 4 other ranges.

As you can see, when completed I want to address 6 different cell ranges on the same worksheet and have only certain contents cleared and subsequent cells moved up. The workbook is too large to post but here is my code so far.

Oh, and if you can suggest code improvements, I would be ever so grateful. I am not a programmer - just a lowly engineer trying to wear a programming hat!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Range1 As Range
Dim Range2 As Range
Dim Range3 As Range
Dim Range4 As Range
Dim Range5 As Range
Dim Range6 As Range
Dim IntersectRange1 As Range
Dim IntersectRange2 As Range
Dim IntersectRange3 As Range
Dim IntersectRange4 As Range
Dim IntersectRange5 As Range
Dim IntersectRange6 As Range




Set Range1 = Range("J3:J16")
Set Range2 = Range("T3:T16")
Set Range3 = Range("J20:J32")
Set Range4 = Range("T20:T32")
Set Range5 = Range("J36:J50")
Set Range6 = Range("T36:T50")


Set IntersectRange1 = Intersect(Target, Range1)
Set IntersectRange2 = Intersect(Target, Range2)
Set IntersectRange3 = Intersect(Target, Range3)
Set IntersectRange4 = Intersect(Target, Range4)
Set IntersectRange5 = Intersect(Target, Range5)
Set IntersectRange6 = Intersect(Target, Range6)
'Application.ScreenUpdating = False


'If IntersectRange1 Is Nothing Then Range2Completion Commented out for debug purposes
If IntersectRange1 Is Nothing Then Exit Sub 'For debug purposes only


If IntersectRange1 = Range("J3") Then
    Application.EnableEvents = False
    Range("A3:J3").ClearContents
    Range("A4:I16").Copy
    Range("A3:I15").PasteSpecial xlPasteValues
    Range("A16:I16").ClearContents
    Range("A3").Select
    Application.EnableEvents = True
End If
If IntersectRange1 = Range("J4") Then
    Application.EnableEvents = False
    Range("A4:J4").ClearContents
    Range("A5:I16").Copy
    Range("A4:I15").PasteSpecial xlPasteValues
    Range("A16:I16").ClearContents
    Range("A3").Select
    Application.EnableEvents = True
End If
If IntersectRange1 = Range("J5") Then
    Application.EnableEvents = False
    Range("A5:J5").ClearContents
    Range("A6:I16").Copy
    Range("A5:I15").PasteSpecial xlPasteValues
    Range("A16:I16").ClearContents
    Range("A3").Select
    Application.EnableEvents = True
End If
If IntersectRange1 = Range("J6") Then
    Application.EnableEvents = False
    Range("A6:J6").ClearContents
    Range("A7:I16").Copy
    Range("A6:I15").PasteSpecial xlPasteValues
    Range("A16:I16").ClearContents
    Range("A3").Select
    Application.EnableEvents = True
End If
If IntersectRange1 = Range("J7") Then
    Application.EnableEvents = False
    Range("A7:J7").ClearContents
    Range("A8:I16").Copy
    Range("A7:I15").PasteSpecial xlPasteValues
    Range("A16:I16").ClearContents
    Range("A3").Select
    Application.EnableEvents = True
End If
If IntersectRange1 = Range("J8") Then
    Application.EnableEvents = False
    Range("A8:J8").ClearContents
    Range("A9:I16").Copy
    Range("A8:I15").PasteSpecial xlPasteValues
    Range("A16:I16").ClearContents
    Range("A3").Select
    Application.EnableEvents = True
End If
If IntersectRange1 = Range("J9") Then
    Application.EnableEvents = False
    Range("A9:J9").ClearContents
    Range("A10:I16").Copy
    Range("A9:I15").PasteSpecial xlPasteValues
    Range("A16:I16").ClearContents
    Range("A3").Select
    Application.EnableEvents = True
End If
If IntersectRange1 = Range("J10") Then
    Application.EnableEvents = False
    Range("A10:J10").ClearContents
    Range("A11:I16").Copy
    Range("A10:I15").PasteSpecial xlPasteValues
    Range("A16:I16").ClearContents
    Range("A3").Select
    Application.EnableEvents = True
End If
If IntersectRange1 = Range("J11") Then
    Application.EnableEvents = False
    Range("A11:J11").ClearContents
    Range("A12:I16").Copy
    Range("A11:I15").PasteSpecial xlPasteValues
    Range("A16:I16").ClearContents
    Range("A3").Select
    Application.EnableEvents = True
End If
If IntersectRange1 = Range("J12") Then
    Application.EnableEvents = False
    Range("A12:J12").ClearContents
    Range("A13:I16").Copy
    Range("A12:I15").PasteSpecial xlPasteValues
    Range("A16:I16").ClearContents
    Range("A3").Select
    Application.EnableEvents = True
End If
If IntersectRange1 = Range("J13") Then
    Application.EnableEvents = False
    Range("A13:J13").ClearContents
    Range("A14:I16").Copy
    Range("A13:I15").PasteSpecial xlPasteValues
    Range("A16:I16").ClearContents
    Range("A3").Select
    Application.EnableEvents = True
End If
If IntersectRange1 = Range("J14") Then
    Application.EnableEvents = False
    Range("A14:J14").ClearContents
    Range("A15:I16").Copy
    Range("A14:I15").PasteSpecial xlPasteValues
    Range("A16:I16").ClearContents
    Range("A3").Select
    Application.EnableEvents = True
End If
If IntersectRange1 = Range("J15") Then
    Application.EnableEvents = False
    Range("A15:J15").ClearContents
    Range("A16:I16").Copy
    Range("A15:I15").PasteSpecial xlPasteValues
    Range("A16:I16").ClearContents
    Range("A3").Select
    Application.EnableEvents = True
End If
Application.ScreenUpdating = True
End Sub

Re: [b]Clearing Contents of Row and Moving all subsequent Rows Up

Thanks so very much! Pretty simple solution, huh? I just couldn't find it...

And thanks for the correction on posting code. I must have just spaced out the forward slash.

Thanks again!

Re: Clearing Contents of Row and Moving all subsequent Rows Up

I will try my best to attach my workbook. I have never done that before and not sure how to do it. As you can see, I've just taken a first stab at this and only am testing one cell. The code will be modified as soon as I have things ironed out.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Range1 As Range
Dim Range2 As Range
Dim Range3 As Range
Dim Range4 As Range
Dim Range5 As Range
Dim Range6 As Range
Dim IntersectRange1 As Range
Dim IntersectRange2 As Range
Dim IntersectRange3 As Range
Dim IntersectRange4 As Range
Dim IntersectRange5 As Range
Dim IntersectRange6 As Range




Set Range1 = Range("J3:J16")
Set Range2 = Range("T3:T16")
Set Range3 = Range("J20:J32")
Set Range4 = Range("T20:T32")
Set Range5 = Range("J36:J50")
Set Range6 = Range("T36:T50")


Set IntersectRange1 = Intersect(Target, Range1)
Set IntersectRange2 = Intersect(Target, Range2)
Set IntersectRange3 = Intersect(Target, Range3)
Set IntersectRange4 = Intersect(Target, Range4)
Set IntersectRange5 = Intersect(Target, Range5)
Set IntersectRange6 = Intersect(Target, Range6)
Application.ScreenUpdating = False


If IntersectRange1 Is Nothing Then Exit Sub




If IntersectRange1 = Range("J4") Then
    Range("A4:I4").ClearContents
    Range("A5:I16").Copy
    Range("A4:I15").PasteSpecial xlPasteValues
    Range("A16:I16").ClearContents
    Range("A3").Select
End If
Application.ScreenUpdating = True
End Sub

I have a macro using the change event. If an "x" gets typed into a certain cell, then the contents of the cells to the left gets cleared and the contents below get moved up so there is not a blank row in the range. All works fine except when I clear the contents of the cell that contains the "x", the change event gets triggered again in a continuous loop and all the cells are moved up and cleared one by one. However if I don't include the cell with the "x", then the "x" remains which I don't want to have happen. Does this make sense to anyone out there? There must be some simple work around.

Thanks so much in advance!

Re: Transferring data upon user input

Thanks a million for your help, now all i need to figure out is how to activate the macro. Can't do it with a workbook activation because the workbook isn't always there. I want the info immediately transferred upon users input and it is a data validaton field. I'll just keep working at it.

I have a workbook which is used company wide. Some worksheets are only created with VBA code when needed and are not permanent workbook sheets. One of these sheets includes user selected items from a list. Those selections are made after the worksheet has been created. At the time the user makes a selection, I need to have that data transferred to another worksheet to be used later in yet another user creaated worksheet. The data will be stored within Excel for later retrieval. How do I activate this behind the scenes? I don't want the user to have to run a macro at this point.

If this is just as clear as mud, I will be happy to try to illuminate the problem further.

Thanks for all the help!

Re: Worksheet Auto Sizing

Thanks for the help. I did have the code individually assigned to each sheet but I was attempting to get all the data into the window instead of limiting the number of rows which I have now done. Works just great!

Re: Worksheet Auto Sizing

Thanks, Thomas

I have tried your method and while it does help the situation, the width of the application window still needs to be adjusted. Is there a way around this?

Your help is much appreciated!

Hi there!

I hope that this problem has not been covered in the past but I could not find exactly what I need to do. First of all, I have a multi-sheet workbook used by other employees all with different resolutions on their monitors. I want each sheet of my workbook to open with the entire width of data viewed. Unfortunately, each sheet varies in width. I have set a scroll area for each sheet so I am not concerned about them scrolling beyond the data. I have tried setting a pixle width for each sheet but this just doesn't work with different resolutions. I am wondering if there is some way to have the area viewed in relation to a cell range? I have been looking but so far nada! With the present method, it is necessary to resize each sheet to be able to see all the data which is not a good thing.

Thanks so much for your help!

ok, I think this will be a good question for some guru out there! I have a sheet that is created with VBA code only as needed in the workbook. Some of the cells on this newly created sheet are controlled by Data Validation. After the sheet has been created, the user selects an item from a list.

After that selection has been made, the spreadsheet needs to record that selection on another (always existing) sheet from which other calculations are then done.

Of course, when I try to use a formula on the aforementioned sheet, I get a #N/A since the control sheet has not been created yet. If I try to use a user function, SheetExists, I seem to create links to other worksheets. This is not acceptable. Then when I break the links, the formula is eliminated and I am back to square one.

I hope I have been clear and also hope someone has a good answer.

It is hard to post this since it is quite a large template and because the contents are very sensitive to our industry and I am not allowed to share a lot of the formulas contained in it. The workbook template is engineering oriented if that means anything to anyone...

Thanks very much!

Ok, either I have not provided a very good example or I have irritated all of you out there which I REALLY hope is not the case! So I am changing the example provided to look a little more like the real thing. I don't dare publish that or my job will be history.

I would sure be happy for someone to jump in here. DK's code helped but didn't completely solve the problem and I have blown away a lot of worksheets in error trying to get it to behave the way I want it to.

H...E...L...P!!!! :coolwink:

I am reposting this to move it back up front since I am still struggling with this and would really appreciate some help!
:flower:

Please, please!

I appologize, dk, your code did accomplish the size listing correctly. Saw that after I posted the above. Now I just need it to skip the rows whose quantity equals 0.

:guitar:

Thanks so much, dk! Your help is so greatly appreciated. I can use this as a start. Doesn't quite do what I would like but closer than I was able to get. I didn't mention that only the first column (qty) might be blank. There is text in the second column and this macro includes it when I want it skipped. Also I have some rows in the source sheet that I need to have skipped (interim headings - these are always constant). Also I would like the destination line toDrive Bracket Supports - 1" x 24" x 40" Long where only the numbers are included on the source sheet.

Thanks so much again...

I really thought I had added an attachment but it didn't get included probably because I'm not sure how to do that. I selected the file off my hard drive as the attachment but didn't get included. I am trying again.

I have a workbook where I have a list of items in one column on one worksheet with a thickness, width, length and weight of that item. Not all the listed items are available for a given application so there might me a line followed by several blanks followed by 5 lines and so one. This always varies. I want to transfer all the listed items along with their descriptions to another worksheet in the workbook but I want each line filled consecutively on that sheet and I want to concatenate the description and put the weight in a seperate cell.

In short, I want to loop thru the entire first sheet, extracting data and creating entries on the second sheet.

I am sure this is just as clear as mud! My descriptive abilities leave something to be desired. So I will try to attach an example.

Hope someone can help. I'm pretty sure this is very simple but I am still struggling with VBA code.

:flower:

I went back and attempted one last time and this time, for whatever reason, it worked. Who knows! But thanks for the help!!!!

I have done so repeatedly! Both from the insert/name/define menu and just by renaming it in the name box that appears when the textbox is selected. The name keeps reverting back to "textbox 82". What might I be doing wrong????

This is probably a really basic question, but I want to use a textbox to display a help file and control the visibility with a macro. How do I name the textbox or do I just have to use the default number?

I have an example where the textbox has been named HelpText and I have no clue how that was accomplished! :no: