Re-sizing Issue

[DRX1234]

Hi. I have a very handy code that someone on this forum was kind enough to write for me. The code automatically hides columns based on user input. It works perfectly. I type a number (1-10) into a cell, and then depending on the number, a corresponding number of columns hide.

The issue is, when i type anything in any of the last couple of columns (the columns that are off-screen and I need to scroll over to the right to see - columns H-L), after inputting anything into any cell within that column, Excel brings me way far over to the far right, well past any visible columns and I have to scroll back over to input the next cell of data.

I'd like the screen view to stay on whatever column I am working within. Here is the code:

Workbook:
Private Sub Workbook_Open()
Dim C6 As Range
With Worksheets("Submission Form")
Set C6 = .Range("C6")
.Columns("D").Resize(, Columns.Count - 3).Hidden = True
C6.Select
If IsNumeric(C6.Value) And Len(C6.Value) > 0 Then
If C6 > 1 And C6 < 11 Then Columns("D").Resize(, C6 + (C6 > 1)).Hidden = False
End If
End With
End Sub

Worksheet:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim C6 As Variant
C6 = Range("C6").Value
If IsNumeric(C6) And Len(C6) > 0 Then
Columns("D:L").Hidden = True
If C6 > 1 And C6 < 11 Then Columns("D").Resize(, C6 + (C6 > 1)).Hidden = False
End If
End Sub

Any suggestions on how to modify?

Thanks in advance!!

 

[venkat1926]

What are you tryig to
Take the code

.Columns("D").Resize(, Columns.Count - 3).Hidden = True

The address of this resized range is $D:$XFD(right most column) because it takes all the columns in the sheet.
So you have to define a new variab
Set r=.range(“A1”).current region
r is data range
dmodify resize code as

.Columns("D").Resize(, r.Columns.Count - 3).Hidden = True

that is r.columns.count this will hide from D to G (last column of data)
Next resize column you have given c6 +c6>1
What is this c6>1 it is not a number. how can you add it . it will give some wrong address
What are you thinking
Better tell the newsgroup what exactly you want and also POST SMALL EXTRACT OF DATA

IS EVENT CODE WORKBOOKS.OPEN eceessary or you can use a standard macro(s)

introduce message boxes of range addresses, range value etc so that it is easy to debug

 

[DRX1234]

Hello. Thank you for your response. I am building a sample submission form. When you open the file, Columns A, B and C are visible. Nothing happens in either column A or B; I need them to remain unhidden. In Column C, cell 6; there is a field that asks for the number of samples to be submitted; a user can input the range of numbers 1 through 10. Below that cell in Column C, reside general questions related to the sample attributes. These same questions are repeated in Columns D-L. If a user enters the number "2" in Cell C6, Column C stays visible and Column D unhides. If they enter a "3", Column C stays visible and Columns D & E unhide. If they enter a "4", Column C stays visible and Columns D, E & F unhide and so on and so forth.

When (depending on the physical screen size of the PC being used) multiple samples are being submitted (>6), you'll need to scroll to the right to input the sample attributes in the appropriate Column, however, the screen view changes and makes the spreadsheet difficult to use. After each cell input, Excel forces you to re-find your place in the spreadsheet. I don't know enough about coding to tell if Event Code Workbooks.Open is necessary. I apologize but my company blocks the HTML maker and I'm not sure how else to post a snapshot or the workbook.

 

[venkat1926]

to work on this i have to prepare a sheet with numbers. will it not be helpful to help you if you ca give a small extract of your data ( a few rows including column A B C and few columns D to L so that I can work on it. if you are particular of security of data fill them up with some random data.

 

[DRX1234]

OK, try this: They didn't come through but there are comboboxes in cells C21:D27

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L
1	XXX MICROBIOLOGY: SAMPLE ANALYSIS REQUEST FORM
2
3	SAMPLE LOG-IN INFORMATION	Date of Submission:
4		Project Number:
5		PO: #
6		# of Samples Being Submitted:	10
7		Testing Facility:
8
9	SUBMITTER INFORMATION	Name:
10		Dept:
11		Phone #:
12		email:
13
14	PRODUCT INFORMATION	Sample Attributes	Sample 1	Sample 2	Sample 3	Sample 4	Sample 5	Sample 6	Sample 7	Sample 8	Sample 9	Sample 10
15		Product Name
16		Formula #
17		Sample Code
18		Date of Manufacture
19		# of containers produced
20		Micro-Sensitive Ingredients (Yes/No)
21		Process Type
22		Sample Type
23		Reason for Submission
24		Test Demographic
25		Distribution Control
26		Location Produced
27		Preserved
28		pH
29		Brix
30		Juice %
31		Co2 Level
32		Water Activity
33		% Moisture
34	Additional info if submitting Raw Ingredients or Type Samples:	Manufacturer Name
35		Lot Number
36	Thermal Process Conditions (if applicable):	Pasteurization Time (min)
37		Pasteurization Temp (?F)
38	Notes Field	If "Other" was selected above, specify here
40
41	SAMPLE INFORMATION (To Be Completed by Microbiology)
42	MICRO INFORMATION	Test Selection	Sample 1	Sample 2	Sample 3	Sample 4	Sample 5	Sample 6	Sample 7	Sample 8	Sample 9	Sample 10
43		APC
44		Yeast
45		Mold
46		Coliform
47		E. coli
48		Salmonella
49		Staphylococcus aureus (coag +)
50		Enterobacteriaceae
51		OSA
52		TPC
53		MFmGreen
54		Commercial Sterility
55		Genus Listeria
56		Listeria monocytogenes
Submission Form

 

[DRX1234]

Hello Board, any suggestions on how to improve my spreadsheet? I am building a sample submission form and using a code to hide columns (code is in first posting above). When you open the file, Columns A, B and C are visible. Nothing happens in either column A or B; I need them to remain unhidden. In Column C, cell 6; there is a field that asks for the number of samples to be submitted; a user can input the range of numbers 1 through 10. Below that cell in Column C, reside general questions related to the sample attributes. These same questions are repeated in Columns D-L. If a user enters the number "2" in Cell C6, Column C stays visible and Column D unhides. If they enter a "3", Column C stays visible and Columns D & E unhide. If they enter a "4", Column C stays visible and Columns D, E & F unhide and so on and so forth.

When (depending on the physical screen size of the PC being used) multiple samples are being submitted (>6), you'll need to scroll to the right to input the sample attributes into the appropriate Column, however, the screen view changes and makes the spreadsheet difficult to use. After each cell input, Excel forces you to re-find your place in the spreadsheet. To re-find your place, you'll need to press the up or down arrow and Excel will take you back to a view of the column you were working in.

Hopefully I've explained this clearly enough. Snapshot of the file above. Thanks!!

 

[venkat1926]

not clear to me. wait for some expert to suggest a solution.
regarding the designing of spreadsheet. see this

Excel/VBA Golden Rules. These Should NOT Be Optional

 

[DRX1234]

In case anyone is interested, the solve is as follows:

Private Sub worksheet_change(ByVal target As Range)
If Not Intersect(Range("C6"), target) Is Nothing Then
Columns(4).Resize(, Columns.Count - 4).Hidden = True
Columns(3).Resize(, Range("C6") + 0).Hidden = False
End If
End Sub