Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro interaction
Hi all:
I am automating much of the work on my sheets with macros. To date, the vast majority of the macros have been created using the Macro Recorder, then judicious editing. This works well for most of the work but there are several items that I cannot figure out: 1. I would like to set the width of a column based on the contents of a cell in that column. i.e.: If the day-of-the-week cell in the column is a 1 or 7 (Sunday or Saturday), I would like to set the width of that column to 3. 2. I would like the macro to ask the user for information to be placed in the sheet. Please let me know if this is easy to do. Thanks, Craig |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro interaction
hi
1. Columns("C:C").ColumnWidth = 3 ' or maybe 3.1 2. Dim r As Range Set r = Cells(1, 1) 'A1 r = InputBox("enter some imformation to put on the sheet.") regards FSt1 "C Brandt" wrote: Hi all: I am automating much of the work on my sheets with macros. To date, the vast majority of the macros have been created using the Macro Recorder, then judicious editing. This works well for most of the work but there are several items that I cannot figure out: 1. I would like to set the width of a column based on the contents of a cell in that column. i.e.: If the day-of-the-week cell in the column is a 1 or 7 (Sunday or Saturday), I would like to set the width of that column to 3. 2. I would like the macro to ask the user for information to be placed in the sheet. Please let me know if this is easy to do. Thanks, Craig |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro interaction
hi again
you might want to use autofit... Columns("C:C").EntireColumn.AutoFit regards FSt1 "FSt1" wrote: hi 1. Columns("C:C").ColumnWidth = 3 ' or maybe 3.1 2. Dim r As Range Set r = Cells(1, 1) 'A1 r = InputBox("enter some imformation to put on the sheet.") regards FSt1 "C Brandt" wrote: Hi all: I am automating much of the work on my sheets with macros. To date, the vast majority of the macros have been created using the Macro Recorder, then judicious editing. This works well for most of the work but there are several items that I cannot figure out: 1. I would like to set the width of a column based on the contents of a cell in that column. i.e.: If the day-of-the-week cell in the column is a 1 or 7 (Sunday or Saturday), I would like to set the width of that column to 3. 2. I would like the macro to ask the user for information to be placed in the sheet. Please let me know if this is easy to do. Thanks, Craig |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro interaction Thanks
Don,
Thanks for the input. I used both your solutions and they worked as advertized (except I had to remove the "Exit For" from the loop, otherwise it would exit when it hit the first Sat or Sun). btw - I haven't attended any formal school for 41 years, so this was not technicaly homework, although it added greatly to my understanding of vba and excel. The company I work for tracks all their efforts with excel.This is a tremendous burden in which macros and VBA are a big help, since every month we have to create new sheets for the month. Thanks Again, Craig "Don Guillett" wrote in message ... Sounds a bit like homework but 1. a looping macro would do Sub columnwidthday() For Each c In Range("b2:b22") If Weekday(c) = 1 Or Weekday(c) = 7 Then 'MsgBox c.Address c.ColumnWidth = 3 Exit For End If Next c End Sub 2. Sub askforvalue() Range("b1").Value = InputBox("Enter value for b1") End Sub -- Don Guillett SalesAid Software "C Brandt" wrote in message ... Hi all: I am automating much of the work on my sheets with macros. To date, the vast majority of the macros have been created using the Macro Recorder, then judicious editing. This works well for most of the work but there are several items that I cannot figure out: 1. I would like to set the width of a column based on the contents of a cell in that column. i.e.: If the day-of-the-week cell in the column is a 1 or 7 (Sunday or Saturday), I would like to set the width of that column to 3. 2. I would like the macro to ask the user for information to be placed in the sheet. Please let me know if this is easy to do. Thanks, Craig |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro interaction Thanks
About the exit for. ?????. I thought you wanted to make the column a certain
width if any cell in the range met the qualifier. if so, it is a WASTE of time to continue to look for other instances. You would want it to stop when it found the first qualifier. -- Don Guillett SalesAid Software "C Brandt" wrote in message ... Don, Thanks for the input. I used both your solutions and they worked as advertized (except I had to remove the "Exit For" from the loop, otherwise it would exit when it hit the first Sat or Sun). btw - I haven't attended any formal school for 41 years, so this was not technicaly homework, although it added greatly to my understanding of vba and excel. The company I work for tracks all their efforts with excel.This is a tremendous burden in which macros and VBA are a big help, since every month we have to create new sheets for the month. Thanks Again, Craig "Don Guillett" wrote in message ... Sounds a bit like homework but 1. a looping macro would do Sub columnwidthday() For Each c In Range("b2:b22") If Weekday(c) = 1 Or Weekday(c) = 7 Then 'MsgBox c.Address c.ColumnWidth = 3 Exit For End If Next c End Sub 2. Sub askforvalue() Range("b1").Value = InputBox("Enter value for b1") End Sub -- Don Guillett SalesAid Software "C Brandt" wrote in message ... Hi all: I am automating much of the work on my sheets with macros. To date, the vast majority of the macros have been created using the Macro Recorder, then judicious editing. This works well for most of the work but there are several items that I cannot figure out: 1. I would like to set the width of a column based on the contents of a cell in that column. i.e.: If the day-of-the-week cell in the column is a 1 or 7 (Sunday or Saturday), I would like to set the width of that column to 3. 2. I would like the macro to ask the user for information to be placed in the sheet. Please let me know if this is easy to do. Thanks, Craig |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how set up regression for interaction? | Excel Discussion (Misc queries) | |||
Excel and web interaction | Excel Discussion (Misc queries) | |||
chart/worksheet/macro interaction | Charts and Charting in Excel | |||
macro/worksheet/chart interaction | Excel Discussion (Misc queries) | |||
... interaction with Word ... | Excel Discussion (Misc queries) |