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!
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
