Spell Check in Cell based on user inputs on specific and multiple worksheets

[gmalpani]

Hi

I have to create a automation in excel to run a spell check before they close or save the excel file. So as none of my team members forgets to run spell check.

I have a code to that, but that works for all worksheets in workbook.
I need to modify it a bit.
- It should take inputs from user for name of worksheets to be reviewed. Please note that user should be allowed to enter one or multiple worksheets name. (Max could be 10)
- Using that name as input, it macro should run only for these worksheets.

My code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim iRet As Integer
    Dim strPrompt As String
    Dim strTitle As String
 
    ' Promt
    strPrompt = "Do you want to run Spell Check"
 
    ' Dialog's Title
    strTitle = "Spell Check"
    ' Display MessageBox
    iRet = MsgBox(strPrompt, vbYesNo, strTitle)
 
    ' Check pressed button
    If iRet = vbNo Then
        MsgBox "ok"
    Else
    Dim sh As Worksheet, currSh As Worksheet
    Set currSh = ActiveSheet
    For Each sh In Me.Worksheets
    sh.Activate
    sh.Cells.CheckSpelling
    Next sh
    currSh.Activate
    End If
End Sub


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim iRet As Integer
    Dim strPrompt As String
    Dim strTitle As String
 
    ' Promt
    strPrompt = "Do you want to run Spell Check"
 
    ' Dialog's Title
    strTitle = "Spell Check"
    ' Display MessageBox
    iRet = MsgBox(strPrompt, vbYesNo, strTitle)
 
    ' Check pressed button
    If iRet = vbNo Then
        MsgBox "ok"
    Else
    Dim sh As Worksheet, currSh As Worksheet
    Set currSh = ActiveSheet
    For Each sh In Me.Worksheets
    sh.Activate
    sh.Cells.CheckSpelling
    Next sh
    currSh.Activate
    End If
End Sub

Also, please help me how can I distribute to my team members and make this macro available for all excel files.

Regards,
gmalpani

 

[gmalpani]

Hi,

I have solved this issue. So, please consider it as closed.
Here is the code I used.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim iRet As Integer
    Dim strPrompt As String
    Dim strTitle As String
 
    ' Promt
    strPrompt = "Do you want to run Spell Check"
 
    ' Dialog's Title
    strTitle = "Spell Check"
    ' Display MessageBox
    iRet = MsgBox(strPrompt, vbYesNo, strTitle)
 
    ' Check pressed button
    If iRet = vbNo Then
        MsgBox "Close File"
    Else
    Dim ReviewSheet As String
    ReviewSheet = Application.InputBox(Prompt:="Please provide worksheet name for Spell Check")
    Worksheets(ReviewSheet).Activate
    Cells.CheckSpelling
    End If
End Sub


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim iRet As Integer
    Dim strPrompt As String
    Dim strTitle As String
 
    ' Promt
    strPrompt = "Do you want to run Spell Check"
 
    ' Dialog's Title
    strTitle = "Spell Check"
    ' Display MessageBox
    iRet = MsgBox(strPrompt, vbYesNo, strTitle)
 
    ' Check pressed button
    If iRet = vbNo Then
        MsgBox "Close File"
    Else
    Dim ReviewSheet As String
    ReviewSheet = Application.InputBox(Prompt:="Please provide worksheet name for Spell Check")
    Worksheets(ReviewSheet).Activate
    Cells.CheckSpelling
    End If
End Sub


Thanks,
gmalpani