One Excel Form for All worksheets Lists

[Arishy]

The Objective: A very User friendly workbook
What I have is list in every worksheet Same Design
Let us say supplier#, Item# and price

To make it easy for myself I used dynamic range for each

What I need to do is to have a front worksheet call it Home that have a
button for every supplier (or a list box),when you select a supplier
Excel builtin FORM appears that let you change, add or delete any item
for that supplier. The user sees only the home sheet with the form for
that supplier.

Now the difficult part: If a New supplier crops up, a new worksheet is
created named with his code and a the SAME FORM appears to let him/her
add the new data.

I built the supplier sheets also a summary sheet to merge all items for
easy VLookup I created the macro that merge them.

Now I am faced with user friendly FORM to allow updates.

I saw few samples , one I liked has the name "database" for all the
work sheet list !!! Which I did not understand

Appreciate your help in getting this Form VBA'd

 

[Simon Lloyd]

Arishy,

You need to post the code and/or workbook as you have it, that way you
will be assured of getting some help with this project!

Simon

 

[Norman Jones]

Hi Simon,

You need to post the code and/or workbook

For excellent reasons, the posting of attachments is actively discouraged in
this NG. In any event, it would not be good advice because few in this group
would be prepared to open unknown attachments.

It is, in my experience, rare that the posting of relevant code together
with a suitable articulation of the encounterd problem proves insufficient
to enable a satifactory solution to be offered.

---
Regards,
Norman



"Simon Lloyd" <[email protected]>
wrote in message

 

[samir arishy]

You asked for Here is the difficult part:

Background:

There is few sheets with name SUPxx xx represent the supplier code (
always 2 digits)

I need to create the following sub
To do the following:
- ask for new number (new supplier) yy
-check it is unique ( I have a range X1-X? that containes all the
existing numbers in Sheet called Home
- create a new worksheet with SUPyy
-Create a heading for the worksheet A1-D1 copied from sheet1
-Call the Excel Form to start inputting data at Row2 of SUPyy


The Code (If you can call it that!!!):

Sub AddNewSupplier()
'
' AddNewSupplier Macro
' Macro recorded 07/08/2005 by Arishy
'

'
Dim newsup As String
newsup = Application.InputBox("Insert a number", "This accepts numbers
only", 1)
'did not code the validation yet
Sheets.Add
Sheets("SUP" & newsup).Select
Sheets("Sheet" & newsup).Name = "SUP"&newsup
Range("A1").Select
Sheets("Sheet1").Select
Range("A11").Select
Selection.Copy
Sheets("SUP"&newsup).Select
ActiveSheet.Paste
Application.CutCopyMode = False
'The following is a key part that create a name '= "Database"
'This will insure that Excel Form knows where the database
'is

ActiveWorkbook.Names.Add Name:="Database", RefersToR1C1:= _
"=OFFSET(!R1C1,0,0)"
showdataform
End Sub

AS you will notice I used the Macro Recorder since I am very new to VBA.

 

[Don Guillett]

see if this helps

Sub addsheet()
On Error GoTo havedup
Worksheets.Add.Name = "Sup" & InputBox("Insert number")
Sheets("Sheet1").Range("A11").Copy Range("a1")
'more explanation about the range name???
havedup: MsgBox ("Already there")
End Sub

 

[Norman Jones]

Hi Samir,

You already have a response. Here is another:

'============================>>
Public Sub Tester()

Dim RngCodes As Range
Dim res As Variant
Dim sh As Worksheet
Dim sStr As String

With Sheets("Home")
Set RngCodes = _
Range(.Cells(1, "X"), .Cells(1, "X").End(xlDown))
End With

Do
res = Application.InputBox _
("Enter a two-digit supplier code", Type:=2)

If res = "False" Then
MsgBox prompt:="You Cancelled!"
Exit Sub
ElseIf Len(res) <> 2 Then
MsgBox "A 2-digit entry is required"
End If

Loop Until Len(res) = 2 And IsNumeric(res)

If IsError(Application.Match(res, RngCodes, 0)) Then
sStr = "Supp" & CStr(res)

If SheetExists(sStr) Then
MsgBox "Found"
'Sheet already exists! _
'What do you want to do?!
Else
Worksheets.Add.Name = sStr
End If

Set sh = Sheets(sStr)
If Application.CountA(RngCodes(1).Resize(2)) = 0 Then
'No existing codes in Code range!
Set RngCodes = Sheets("Home").Range("X1")
Else
Set RngCodes = RngCodes. _
Resize(RngCodes.Count + 1)
End If

RngCodes(RngCodes.Cells.Count).Value = sStr
End If

sh.Range("A11").Value = _
Sheets("Sheet1").Range("A11").Value

With Application
.DisplayAlerts = False
.Goto sh.Range("A1")
sh.ShowDataForm
.DisplayAlerts = True
End With

End Sub

Function SheetExists(sName As String, _
Optional ByVal wb As Workbook) As Boolean
On Error Resume Next
If wb Is Nothing Then Set wb = ActiveWorkbook
SheetExists = CBool(Len(Sheets(sName).Name))
End Function
'<<============================