Posts by Tommaso

Tommaso · Apr 12th 2023

Hi everyone:

I'm using a VBA code for a dropdown list with multiple options in Excel, but when I enter text manually in a cell that already contains one or more selected options, the code duplicates the previous selections and then adding the new text. For example, if I select "Option 1" and "Option 2" from the dropdown list, the cell displays "Option 1, Option 2". However, if I then type "text" in the same cell and click on another cell, the cell displays "Option 1, Option 2, Option 1, Option 2, text".

I'd like to modify the code to prevent this behavior and maintaining the selections Option 1, Option 2 with the new text when the cell is edited manually. Here's the current code I'm using:

Code

Option Explicit

Private Sub Worksheet_Change(ByVal Destination As Range)
    Dim rngDropdown As Range
    Dim oldValue As String
    Dim newValue As String
    Dim DelimiterType As String
    
    DelimiterType = ", "
     
    If Destination.Count > 1 Then Exit Sub
     
    On Error Resume Next
    Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo exitError
     
    If rngDropdown Is Nothing Then GoTo exitError
     
    'Check if the cell has data validation or not
    If Destination.Validation.Type = xlValidateList Then
        If Intersect(Destination, rngDropdown) Is Nothing Then
           'do nothing
        Else
          Application.EnableEvents = False
          newValue = Destination.Value
          Application.Undo
          oldValue = Destination.Value
          Destination.Value = newValue
            If oldValue <> "" Then
                If newValue <> "" Then
                    If oldValue = newValue Or _
                        InStr(1, oldValue, DelimiterType & newValue) Or _
                        InStr(1, oldValue, newValue & Replace(DelimiterType, " ", "")) Then
                        Destination.Value = oldValue
                    Else
                        Destination.Value = oldValue & DelimiterType & newValue
                    End If
                End If
            End If
        End If
    End If
     
exitError:
    Application.EnableEvents = True
End Sub
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
End Sub

Display More

In respect to the forum rules, I wanted to mention that I also posted the same question in another forum a few days ago, but have not received an answer there. Here is the link to that post: Prevent VBA from duplicating dropdown list selections when entering manual text. (excelforum.com).

Can anyone help me modify this code to achieve the desired behavior? Thanks in advance for your help!

Tommaso · Mar 22nd 2023

Hello,

I am looking for a way to automate the process of creating a new page in a specific section of OneNote and pasting content from Excel into it. After some research, I found a VBA code that seems to do the job. Here's the code:

Code

Sub CopyToOneNote()

    Dim notebookName As String
    notebookName = "GP Rounds" 'change this to the name of your notebook

    Dim sectionName As String
    sectionName = "Patient Name" 'change this to the name of your section

    Dim pageName As String
    pageName = "New page" 'change this to the name of your new page

    'Get the content from the last row of column B
    Dim content As String
    content = Range("B" & Cells.Rows.count).End(xlUp).value

    'open the OneNote app
    Shell "C:\Program Files\Microsoft Office\root\Office16\ONENOTE.EXE"

    'wait for the OneNote app to open
    Application.Wait (Now + TimeValue("0:00:05"))

    'send the keyboard shortcut to create a new page
    Application.SendKeys ("^n")

    'wait for the new page to be created
    Application.Wait (Now + TimeValue("0:00:02"))

    'send the keyboard shortcut to focus on the page title
    Application.SendKeys ("%p")

    'type the page title
    Application.SendKeys (pageName)

    'navigate to the desired notebook and section
    Application.SendKeys "^g"
    Application.SendKeys notebookName
    Application.SendKeys "%s"
    Application.Wait (Now + TimeValue("0:00:01"))
    Application.SendKeys sectionName
    Application.Wait (Now + TimeValue("0:00:01"))
    Application.SendKeys "{ENTER}"
    Application.SendKeys ("%g")
    Application.SendKeys ("%g")
    Application.SendKeys (pageName)

    'wait for the navigation to complete
    Application.Wait (Now + TimeValue("0:00:02"))

    'copy the content to the Clipboard
    Range("B" & Cells.Rows.count).End(xlUp).Copy

    'send the keyboard shortcut to paste the content into OneNote
    Application.SendKeys ("^v")

End Sub

Display More

However, I am running into an issue where the code directs me to OneNote, but not to the specific section that I want to create the new page in. Instead, it sometimes takes me to a random section or the one that is already opened in the notebook. Could you please advise me on how to modify the code to ensure that it directs me to the correct section in my OneNote notebook?

Thank you!

Best regards,

Tommaso