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

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

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

Macro interaction
 

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

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

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