DDP (Drop down Box)

[K1KKKA]

i have a front sheet that has data input onto it, this then feeds into
sheets Monday thru Sunday,

I would like to print data on occassions and have decided to creat a
form for this option, what i dont want to do is allow users access to
the data on the pages then print, am looking for a safe way to do this
and decided a form, drop down box, selecting the day of the week to be
printed and this would print the required data,

Any idea of the code for example,


Selecting Monday in the Drop down box, would then print the Sheet
"Mon"
Tuesday in DDB would then print "Tue" and so on?


This is a proving to be a great site for assistance with these type of
features and am looking once again for some help



Steve

 

[Bob Phillips]

I would code it for thr prionting to be triggered by an explicit action,
clicking a button, rather than just selecting an item in the drop-dowm.

Private Sub cmdPrint_Click()
If Me.ComboBox1.ListIndex <> -1 Then
Worksheets(Me.ComboBox1.Value).PrintOut
End Sub

In this I added a button and called it cmdPrint, and the days are in
combobox called ComboBox1.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[K1KKKA]

I would code it for thr prionting to be triggered by an explicit action,
clicking a button, rather than just selecting an item in the drop-dowm.

Private Sub cmdPrint_Click()
If Me.ComboBox1.ListIndex <> -1 Then
Worksheets(Me.ComboBox1.Value).PrintOut
End Sub

In this I added a button and called it cmdPrint, and the days are in
combobox called ComboBox1.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)











- Show quoted text -

Bob,

As i read this, It would appear that your code is using the worksheets
as a listindex, is this correct?

Reason i ask, have used this code and nothing appears in DDB,

HYCH

Steve

 

[Bob Phillips]

No, I am using the combox1 value to index into the worksheets collection.

What is DDB?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

Drop Down Box (in the subject)

But I'm not sure if the OP was using a dropdown from the Forms toolbar or a
combobox from the control toolbar--and if that dropdown/combobox was populated
with the sheet names--or if the OP needs help with that.

 

[Bob Phillips]

In the light of what Dave pointed out, you need to populate the drop-down.
Can you give us more details of what you have done?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[K1KKKA]

In the light of what Dave pointed out, you need to populate the drop-down.
Can you give us more details of what you have done?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)







- Show quoted text -

Bob,

Sorry been away,

Having used the code above, if i type a sheet name i.e "Mon" into the
combobox then it runs and prints, but the box remains empty, what am i
missing to populate this?


HYCH
Steve

 

[K1KKKA]

In the light of what Dave pointed out, you need to populate the drop-down.
Can you give us more details of what you have done?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)







- Show quoted text -

I created a form that ask's to select a day to print, ie

mon, tue, wed, thur, fri, sat, sun

I understand using the sheets as a list index, but how would i achieve
popluating the combobox ?


DDB = Drop Down Box

 

[Dave Peterson]

I put a dropdown from the Forms toolbar on a worksheet.

I put the names of the sheets to print in A1:A7 of that same worksheet.

I rightclicked on that dropdown and chose:
Format Control|Control Tab
In the input range, I put
$a$1:$a$7

Then I put this code into a general module:

Option Explicit
Sub testme01()
Dim myDD As DropDown

Set myDD = ActiveSheet.DropDowns(Application.Caller)

With myDD
On Error Resume Next
Worksheets(.List(.ListIndex)).PrintOut preview:=True
If Err.Number <> 0 Then
Beep
MsgBox "Design Error!"
Err.Clear
End If
On Error GoTo 0
End With
End Sub

I used preview:=true to save paper while testing.

Rightclick on the dropdown and choose Assign Macro and assign this macro to the
dropdown.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[K1KKKA]

I put a dropdown from the Forms toolbar on a worksheet.

I put the names of the sheets to print in A1:A7 of that same worksheet.

I rightclicked on that dropdown and chose:
Format Control|Control Tab
In the input range, I put
$a$1:$a$7

Then I put this code into a general module:

Option Explicit
Sub testme01()
Dim myDD As DropDown

Set myDD = ActiveSheet.DropDowns(Application.Caller)

With myDD
On Error Resume Next
Worksheets(.List(.ListIndex)).PrintOut preview:=True
If Err.Number <> 0 Then
Beep
MsgBox "Design Error!"
Err.Clear
End If
On Error GoTo 0
End With
End Sub

I used preview:=true to save paper while testing.

Rightclick on the dropdown and choose Assign Macro and assign this macro to the
dropdown.

If you're new to macros, you may want to read David McRitchie's intro at:http://www.mvps.org/dmcritchie/excel/getstarted.htm










--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave,


5 Stars all the way, works ideal,

Checking out the link now, thanks

Many thanks
Steve