~~ Help with Userform and Combo Box ~~
 

I have no experience with Userforms and combo boxes, but think they will
assist me in achieving what i am trying to do.

I have formatted a Userform with a combo box, but :

How do i activate it from the worksheet ?

I generally use the 'Asign macro' to buttons etc. BUT the userform and combo
box does not show is the list of macro's.

How do i asign the userform to run/display from a button etc ?

Corey....

~~ Help with Userform and Combo Box ~~
 

assign a macro to the button, and in the macro,. add

Userform1.Show

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Corey" wrote in message
...
I have no experience with Userforms and combo boxes, but think they will
assist me in achieving what i am trying to do.

I have formatted a Userform with a combo box, but :

How do i activate it from the worksheet ?

I generally use the 'Asign macro' to buttons etc. BUT the userform and
combo
box does not show is the list of macro's.

How do i asign the userform to run/display from a button etc ?

Corey....

~~ Help with Userform and Combo Box ~~
 

Create another macro in a general module:

Option Explicit
sub showtheform()
userform1.show
end sub

You may want to look at how Debra Dalgleish approached it:
http://www.contextures.com/xlUserForm01.html

Corey wrote:

I have no experience with Userforms and combo boxes, but think they will
assist me in achieving what i am trying to do.

I have formatted a Userform with a combo box, but :

How do i activate it from the worksheet ?

I generally use the 'Asign macro' to buttons etc. BUT the userform and combo
box does not show is the list of macro's.

How do i asign the userform to run/display from a button etc ?

Corey....

--

Dave Peterson

~~ Help with Userform and Combo Box ~~
 

Thanks guy's did the job.
Having problems with setting the Combo Box to look at the Row Source but
will persist a bit before aksing for help.
Regards

Corey....

"Bob Phillips" wrote in message
...
assign a macro to the button, and in the macro,. add

Userform1.Show

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Corey" wrote in message
...
I have no experience with Userforms and combo boxes, but think they will
assist me in achieving what i am trying to do.

I have formatted a Userform with a combo box, but :

How do i activate it from the worksheet ?

I generally use the 'Asign macro' to buttons etc. BUT the userform and
combo
box does not show is the list of macro's.

How do i asign the userform to run/display from a button etc ?

Corey....

~~ Help with Userform and Combo Box ~~
 

Ok.
I have managed to get thus far.
Previously i had a button to run (macro11), this triggered other macro's
(macro2 & macro20) depending
on the amount of sheets in the workbook.

If (macro2) was the option, then an Input box would prompt the user for a
customer name, for a New Sheet.

But i then realised i wanted a LIST of customer names to choose from, to
eliminate a single cuatomer being
entered multiple times as different spellings or variations in names.

So after a previous posting, i was recommended to use a Userform/Combo Box.
So below between the double lines in the Code for the Userform and Combo
Box, that i want to LIST the
customer names from a range in sheet name"1" in my workbook.
Then below that i have the relative macro codes that i am using.
It is the (macro2) that i am needing to be modified to remove the OLD
customer name prompt, and replace
this with the Userform and Combo Box instead, but STILL have the New Sheet
name Indexed as (macro2) does.

At present the Userform/Combo Box works fine, but i then after selecting the
customer name, get the OLD
prompt from (macro2).
I tried to remove the Input Box steps, but then the New Sheet names did not
work correctly??

Any help is appreciated.

I use a button to run Macro66:
================================================== ============
Sub Macro66()
' Shows the Userform number below
' Macro recorded 29/06/2006 by Corey

UserForm1.Show
Call Macro11

End Sub

--------------------------------------------------------------------------------------------
Private Sub ComboBox1_Change()

End Sub
--------------------------------------------------------------------------------------------
Private Sub CommandButton1_Click()
' Call Macro11
' = UserForm1.ComboBox1.Value
UserForm1.Hide
' ReviewItem
End Sub
--------------------------------------------------------------------------------------------
Private Sub CommandButton2_Click()
UserForm1.Hide
End Sub

Private Sub UserForm_Click()

End Sub
================================================== ============

Sub Macro11()
' This macro runs (1) macro if number of sheets is = to (x), or if < (x)
then runs another macro
With ActiveWorkbook
If .Worksheets.Count = 10 Then
Call Macro20
Exit Sub
End If
If .Worksheets.Count < 10 Then
Call Macro2

End If
End With
End Sub

-------------------------------------------------------------------------------------------------

Sub Macro20()
' This macro prompts you that MAX sheets is reached and to Name the New Copy
(sheet1,sheet2)
' Range("A4").Select
' Selection.ClearContents
res = InputBox("MAXIMUM File SIZE REACHED, What do you want to NAME the NEW
file ? ", "Company Name here...")
If res = "" Then Exit Sub
ThisWorkbook.Save
' ThisWorkbook.Close
With ActiveWorkbook
Worksheets(Array("Enter-Exit Page", "1")).Copy
' Application.Dialogs(xlDialogSaveAs) = (res) ' <==== Want to SAVE the
NEW Copy of the WorkBook as the Value placed in the Input Box ????
End With
ActiveWindow.DisplayWorkbookTabs = True
With ActiveWindow
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = False
End With
End Sub

----------------------------------------------------------------------------------------

Sub Macro2()
' Created by Corey on 28/6/2006.... with assistance of course
' This macro prompts for a Customer Name, and calls the new sheet the NEXT
number
Dim sh As Worksheet
Dim msg As String, sName As String
Dim res As String
res = InputBox("What Is the Customer Name ?", "Company Name here....")
If res = "" Then Exit Sub
Do
sName = ActiveWorkbook.Worksheets.Count
If sName = "" Then Exit Sub
On Error Resume Next
Set sh = Worksheets(sName)
On Error GoTo 0
msg = "Nothing here "
Loop While Not sh Is Nothing
With ActiveWorkbook
.Worksheets("1").Copy After:=.Worksheets(.Worksheets.Count)
End With
ActiveSheet.Name = sName
[B3].Select
ActiveCell.Value = res
End Sub

--------------------------------------------------------------------------------------
Corey....

~~ Help with Userform and Combo Box ~~
 

I had trouble following your code and since it's not too long, I think I'd just
embed the procedures directly into the userform module--I wouldn't have used
"Call macroxx" in this case (but it's a personal preference).

Anyway, I also got rid of some of the code that did non-essential stuff. You
can add that back when you get the essential stuff working (saving this
workbook, displaying tabs and scrollbars).

But this may give you more stuff to play with (or not). All this goes behind
the userform:

Option Explicit
Private Sub ComboBox1_Change()
Call CheckToEnableOkButton
End Sub
Private Sub CommandButton1_Click()

Dim myFileName As Variant
Dim NewWksName As String
Dim NewWkbk As Workbook
Dim NewWks As Worksheet

NewWksName = Me.ComboBox1.Value

With ActiveWorkbook
If .Worksheets.Count = 10 Then
myFileName = Application.GetSaveAsFilename _
(filefilter:="Excel Files, *.xls", _
Title:="MAXIMUM File SIZE REACHED, " _
& "What do you want to NAME the NEW file")

If myFileName = False Then
MsgBox "Your file was not saved!!!!" _
& vbLf & "Process cancelled!"
Exit Sub
End If
.Worksheets(Array("Enter-Exit Page", "1")).Copy
Set NewWkbk = ActiveWorkbook
Else
Set NewWkbk = ActiveWorkbook
End If
End With

With NewWkbk 'either the new workbook or the activeworkbook
Set NewWks = Nothing
On Error Resume Next
Set NewWks = .Worksheets(NewWksName)
On Error GoTo 0
If NewWks Is Nothing Then
'doesn't already exist
'keep going
.Worksheets("1").Copy After:=.Worksheets(.Worksheets.Count)
Set NewWks = ActiveSheet
With NewWks
.Name = Me.ComboBox1.Value
.Range("B3").Value = NewWksName
End With
'save the new workbook here
Application.DisplayAlerts = False
.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
Application.DisplayAlerts = True
Else
MsgBox "Worksheet named: " & NewWksName & " already exists." _
& vbLf & "Try again"
End If
End With
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
With ThisWorkbook.Worksheets("Sheet1")
Me.ComboBox1.List _
= .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)).Value
End With
Me.ComboBox1.Style = fmStyleDropDownList
With Me.CommandButton1
.Enabled = False
.Caption = "Ok"
End With

With Me.CommandButton2
.Enabled = True
.Caption = "Cancel"
End With

End Sub
Private Sub CheckToEnableOkButton()
Me.CommandButton1.Enabled = False
If Me.ComboBox1.ListIndex = 0 Then
Me.CommandButton1.Enabled = True
End If
End Sub

Please test the heck out of it. I wasn't quite sure what you were doing so I
could have screwed it up really bad!


Corey wrote:

Ok.
I have managed to get thus far.
Previously i had a button to run (macro11), this triggered other macro's
(macro2 & macro20) depending
on the amount of sheets in the workbook.

If (macro2) was the option, then an Input box would prompt the user for a
customer name, for a New Sheet.

But i then realised i wanted a LIST of customer names to choose from, to
eliminate a single cuatomer being
entered multiple times as different spellings or variations in names.

So after a previous posting, i was recommended to use a Userform/Combo Box.
So below between the double lines in the Code for the Userform and Combo
Box, that i want to LIST the
customer names from a range in sheet name"1" in my workbook.
Then below that i have the relative macro codes that i am using.
It is the (macro2) that i am needing to be modified to remove the OLD
customer name prompt, and replace
this with the Userform and Combo Box instead, but STILL have the New Sheet
name Indexed as (macro2) does.

At present the Userform/Combo Box works fine, but i then after selecting the
customer name, get the OLD
prompt from (macro2).
I tried to remove the Input Box steps, but then the New Sheet names did not
work correctly??

Any help is appreciated.

I use a button to run Macro66:
================================================== ============
Sub Macro66()
' Shows the Userform number below
' Macro recorded 29/06/2006 by Corey

UserForm1.Show
Call Macro11

End Sub

--------------------------------------------------------------------------------------------
Private Sub ComboBox1_Change()

End Sub
--------------------------------------------------------------------------------------------
Private Sub CommandButton1_Click()
' Call Macro11
' = UserForm1.ComboBox1.Value
UserForm1.Hide
' ReviewItem
End Sub
--------------------------------------------------------------------------------------------
Private Sub CommandButton2_Click()
UserForm1.Hide
End Sub

Private Sub UserForm_Click()

End Sub
================================================== ============

Sub Macro11()
' This macro runs (1) macro if number of sheets is = to (x), or if < (x)
then runs another macro
With ActiveWorkbook
If .Worksheets.Count = 10 Then
Call Macro20
Exit Sub
End If
If .Worksheets.Count < 10 Then
Call Macro2

End If
End With
End Sub

-------------------------------------------------------------------------------------------------

Sub Macro20()
' This macro prompts you that MAX sheets is reached and to Name the New Copy
(sheet1,sheet2)
' Range("A4").Select
' Selection.ClearContents
res = InputBox("MAXIMUM File SIZE REACHED, What do you want to NAME the NEW
file ? ", "Company Name here...")
If res = "" Then Exit Sub
ThisWorkbook.Save
' ThisWorkbook.Close
With ActiveWorkbook
Worksheets(Array("Enter-Exit Page", "1")).Copy
' Application.Dialogs(xlDialogSaveAs) = (res) ' <==== Want to SAVE the
NEW Copy of the WorkBook as the Value placed in the Input Box ????
End With
ActiveWindow.DisplayWorkbookTabs = True
With ActiveWindow
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = False
End With
End Sub

----------------------------------------------------------------------------------------

Sub Macro2()
' Created by Corey on 28/6/2006.... with assistance of course
' This macro prompts for a Customer Name, and calls the new sheet the NEXT
number
Dim sh As Worksheet
Dim msg As String, sName As String
Dim res As String
res = InputBox("What Is the Customer Name ?", "Company Name here....")
If res = "" Then Exit Sub
Do
sName = ActiveWorkbook.Worksheets.Count
If sName = "" Then Exit Sub
On Error Resume Next
Set sh = Worksheets(sName)
On Error GoTo 0
msg = "Nothing here "
Loop While Not sh Is Nothing
With ActiveWorkbook
.Worksheets("1").Copy After:=.Worksheets(.Worksheets.Count)
End With
ActiveSheet.Name = sName
[B3].Select
ActiveCell.Value = res
End Sub

--------------------------------------------------------------------------------------
Corey....

--

Dave Peterson

~~ Help with Userform and Combo Box ~~
 

Thanks for the replay Dave,

I have input the code, but get an error at the <== line :



With NewWkbk 'either the new workbook or the activeworkbook
Set NewWks = Nothing
On Error Resume Next
Set NewWks = .Worksheets(NewWksName)
On Error GoTo 0
If NewWks Is Nothing Then
'doesn't already exist
'keep going
.Worksheets("1").Copy After:=.Worksheets(.Worksheets.Count)
Set NewWks = ActiveSheet
With NewWks
.Name = Me.ComboBox1.Value
.Range("B3").Value = NewWksName
End With
'save the new workbook here
Application.DisplayAlerts = False
.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
<================= ERROR ===========
Application.DisplayAlerts = True
Else
MsgBox "Worksheet named: " & NewWksName & " already exists." _
& vbLf & "Try again"
End If
End With
End Sub

I think this is where the New WorkBook should be Named.
THIS is ONLY Needed when the Maximum Number of sheets has reached 10.
ELSE a New WorkSheet is created in the active workbook.

Corey....

~~ Help with Userform and Combo Box ~~
 

Dave,
Just realised that the Userform/Combo Box actually names the SHEET the value
from the Combo Box.

This Value is to be Pasted into the New Sheet cell [B3], and the New Sheet
is Named the Next Indexed Value. (1,2,3,4,5.... etc)



Corey....

~~ Help with Userform and Combo Box ~~
 

This is the portion that names the new sheet:

With NewWks
.Name = Me.ComboBox1.Value '<--- this line does the rename
.Range("B3").Value = NewWksName
End With

But I'm not sure what the error is on that other line.

Any chance you're saving to a location that you don't have write access to?

Corey wrote:

Thanks for the replay Dave,

I have input the code, but get an error at the <== line :

With NewWkbk 'either the new workbook or the activeworkbook
Set NewWks = Nothing
On Error Resume Next
Set NewWks = .Worksheets(NewWksName)
On Error GoTo 0
If NewWks Is Nothing Then
'doesn't already exist
'keep going
.Worksheets("1").Copy After:=.Worksheets(.Worksheets.Count)
Set NewWks = ActiveSheet
With NewWks
.Name = Me.ComboBox1.Value
.Range("B3").Value = NewWksName
End With
'save the new workbook here
Application.DisplayAlerts = False
.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
<================= ERROR ===========
Application.DisplayAlerts = True
Else
MsgBox "Worksheet named: " & NewWksName & " already exists." _
& vbLf & "Try again"
End If
End With
End Sub

I think this is where the New WorkBook should be Named.
THIS is ONLY Needed when the Maximum Number of sheets has reached 10.
ELSE a New WorkSheet is created in the active workbook.

Corey....

--

Dave Peterson

~~ Help with Userform and Combo Box ~~
 

Thanks again Dave.

I have the UserForm and Combo Box working Excatly as required now, except
for
1 thing.
IS THERE A line i can ADD to CLEAR the Combo Box from the Last Entry?

Corey....

~~ Help with Userform and Combo Box ~~
 

Combobox1.ListIndex = -1

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Corey" wrote in message
...
Thanks again Dave.

I have the UserForm and Combo Box working Excatly as required now, except
for
1 thing.
IS THERE A line i can ADD to CLEAR the Combo Box from the Last Entry?

Corey....