Alternative option for a data validation list to a drop down function on an existing sheet with my workbook?

[ShawnSPS]

In my current workbook, a user may press “Add” what it does is copies a worksheet from a hidden worksheet called “ablank” renames the sheet to whatever the user enter as a New client name for example “ABC Company” and then copies that name and places it in Cell “A2” of that sheet. What it does next its copies “A2” and place into a data validation list called “zdata” that is hidden which it then “Znotes” then has the name created in its dropdown list.



Is there an alternative that I can use that does that same without using a data validation list and hiding it to create that drop function in znotes. Also with the Delete button on that workbook it would delete the worksheet but also remove the name from the “zdata” in return removes the name from the drop down list.



I have give you the two macro’s that drives it and my workbook file to understand what its doing in a working environment. Thank you.



Add button macro:



Public Sub CopySheetAndRenamePredefined()

Application.ScreenUpdating = False

Dim newName As String, i As Long

newName = InputBox("Enter New Client Name:")

If newName = "" Then

MsgBox ("User canceled!")

Exit Sub

End If

If Evaluate("isref('" & newName & "'!A1)") Then

MsgBox ("Sheet " & newName & " already exists." & Chr(10) & "Please enter a different name.")

Exit Sub

End If

With Sheets("Zdata")

.Unprotect Password:="Pila1DA.#"

.Cells(.Rows.Count, "A").End(xlUp).Offset(1) = newName

.Protect Password:="Pila1DA.#"

End With

Sheets("Ablank").Visible = True

Worksheets("Ablank").Copy After:=Worksheets(Sheets.Count)

With ActiveSheet

.Unprotect Password:="Pila1DA.#"

.Name = UCase(Trim(newName))

.Range("A2").Value = UCase(Trim(.Name))

.Protect Password:="Pila1DA.#"

End With

For i = 1 To Application.Sheets.Count

For j = 1 To Application.Sheets.Count - 1

If UCase$(Application.Sheets(j).Name) > UCase$(Application.Sheets(j + 1).Name) Then

Sheets(j).Move After:=Sheets(j + 1)

End If

Next

Next

Sheets(newName).Activate

Sheets("Ablank").Visible = False

ActiveWorkbook.Save

Application.ScreenUpdating = True

MsgBox "The tabs have been sorted from A to Z."

End Sub


Delete button Macro



Sub check_sheet_delete()

Application.ScreenUpdating = False

Dim mySheet As String, fnd As Range

mySheet = InputBox("ENTER CLIENT NAME TO DELETE")

If mySheet = "" Then Exit Sub

If Evaluate("isref('" & mySheet & "'!A1)") Then

Application.DisplayAlerts = False

Sheets(mySheet).DELETE

Application.DisplayAlerts = True

With Sheets("Zdata")

.Unprotect Password:="Pila1DA.#"

Set fnd = .Range("A:A").Find(mySheet, LookIn:=xlValues, lookat:=xlWhole)

fnd.DELETE shift:=xlUp

.Protect Password:="Pila1DA.#"

End With

Else

MsgBox ("Sheet " & mySheet & " does not exist.")

End If

Application.ScreenUpdating = False

End Sub

BlankCounts.xls

Shared with Dropbox

www.dropbox.com

 

[ShawnSPS]

Here is a better understand on what I need but still keeping the integrity workbook functioning.

I need a notes button to open “znotes(ws)” it would fill in the date and name of the client from what ever tab(ws) the user was on such as “ABC Company” where the user can fill in what ever note or message it needed for “ABC Company” then a button to go back to “ABC Company” tab so the can filling any additional data they needed to enter.





I do have code in both the Add button and Delete where it use the data valdation list method . it place that company into a list called Zdata. To create the dropdown option for znotes. I would need to scrub from each macro with out ruining the rest of the code it is essential for the function of the workbook so before you do anything please make sure you understand the flow and the reasoning for why it was setup



I want to get rid of the need of using that date validation list and method because it a bit amateurish and it slows now the workbook

 

[ShawnSPS]

I believe I figured out code that works. For testing purposes I have two generic accounts made up to test with "vine" and "ShawnCO" - what i need is have the code drop down for a new entry in znotes when the user clicks on any of the client tabs. yes some tabs do not have the notes .. only when you create or add a new client will notes be visible.

Public Sheetname As String
Sub Appl_Goto()
Sheetname = ActiveSheet.Name
Application.Goto Reference:=Worksheets("Znotes").Range("b2")
With Range("b2")
.Value = Now()
.NumberFormat = "mm/dd/yyyy h:mm:ss AM/PM"
End With
Application.Goto Reference:=Worksheets("Znotes").Range("d2")
With Range("d2")
.Value = Sheetname
End With
Sheetname = ""
End Sub

Blankcounts3.xls

Shared with Dropbox

www.dropbox.com

 

[ShawnSPS]

Hmm pretty quiet here about this question.



Look at my workbook. All I need is to have a “notes” button on my master worksheet (ablank) when pressed is to enter in date and time starting on “b2” and copy the client name which “a2” on any worksheet and paste under the name company each time the user presses the “Notes button” I have it partial done I have the Notes button do a click event to go to Notes .. now I need the vba to copy the data to where I want to put it.



I have to worksheets that have the button already setup as an example and the notes worksheet is listed as on of the tabs on the workbook. Maybe I just not explaining it right. I am a novice after all .

 

[jputman]

Hmm pretty quiet here about this question.



Look at my workbook. All I need is to have a “notes” button on my master worksheet (ablank) when pressed is to enter in date and time starting on “b2” and copy the client name which “a2” on any worksheet and paste under the name company each time the user presses the “Notes button” I have it partial done I have the Notes button do a click event to go to Notes .. now I need the vba to copy the data to where I want to put it.



I have to worksheets that have the button already setup as an example and the notes worksheet is listed as on of the tabs on the workbook. Maybe I just not explaining it right. I am a novice after all .

yeah... I think that what you were actually requesting was getting lost in the weeds here. Tons of details to wade through to get to the real question. No worries... I just hope I am answering the correct question.

I think this is all you need to do to modify what you have to work how you want it to... which is to just find the next blank entry row and add your entry there. Let me know if you needed something different.

Sub Appl_Goto()
    Dim sheetName As String
    Dim notesDateCell As Range
    
    sheetName = ActiveSheet.Name
    Worksheets("Znotes").Activate

    Set notesDateCell = Worksheets("Znotes").Range("B2")    
    While notesDateCell.Value <> ""
        Set notesDateCell = notesDateCell.Offset(1)
    Wend
    
    With notesDateCell
        .Value = Now()
        .NumberFormat = "mm/dd/yyyy h:mm:ss AM/PM"
        .Offset(0, 1).Activate
        .Offset(0, 2).Value = sheetName
    End With
    
End Sub

 

[jputman]

Made a slight adjustment that will work better in the long run... don't need to set the range object over and over, I should have just used range end up:

Sub Appl_Goto()
    Dim sheetName As String
    Dim notesDateCell As Range
    
    sheetName = ActiveSheet.Name
    
    With Worksheets("Znotes")
        .Activate
        Set notesDateCell = .Range("B" & .Rows.Count).End(xlUp).Offset(1)
    End With
    
    With notesDateCell
        .Value = Now()
        .NumberFormat = "mm/dd/yyyy h:mm:ss AM/PM"
        .Offset(0, 1).Activate
        .Offset(0, 2).Value = sheetName
    End With
    
End Sub

 

[ShawnSPS]

Made a slight adjustment that will work better in the long run... don't need to set the range object over and over, I should have just used range end up:

Sub Appl_Goto()
    Dim sheetName As String
    Dim notesDateCell As Range
   
    sheetName = ActiveSheet.Name
   
    With Worksheets("Znotes")
        .Activate
        Set notesDateCell = .Range("B" & .Rows.Count).End(xlUp).Offset(1)
    End With
   
    With notesDateCell
        .Value = Now()
        .NumberFormat = "mm/dd/yyyy h:mm:ss AM/PM"
        .Offset(0, 1).Activate
        .Offset(0, 2).Value = sheetName
    End With
   
End Sub

Thank you, worked perfectly .. I appreciate all the work that it took to get to this answer.

 

[Fluff]

In future please mark the post that contains the solution, rather than your post saying it works. Thanks