Avatar of piyushdabomb

asked on 

Using an Inputbox to Control Workbooks using Excel/VBA

Currently, with the function below, user clicks a button in Sheets(2) and within the same workbook, user selects a cell or a range of cells and those cells are shaded based upon a format. Okay, fine so this works. How can I run this accross multiple workbooks though?

Lets say I have a 'control' workbook. I click the 'Run Macro' button, and the following sequence occurs:

> User selects workbook (easy step)
> Workbook opens and user selects a range in the workbook
> Shades all the cells in the region
> Save the workbook and closes
> Prompts the user to select another workbook
> Workbook opens and user selects a range.... (and keep looping till user doesn't want to select a WB).

Here is the code I used to shade cells:

Public Sub Color_Range()

Dim cell_count As Integer
Dim mycell As Range

Set mycell = Application.InputBox(prompt:="Select a cell or table to shade. If you select just 1" _
                                           & "cell, this macro will determine the table to shade", Type:=8)

cell_count = 0
For Each cell In mycell
    cell_count = cell_count + 1

If cell_count = 1 Then
    Set_Range_States mycell, last_row, last_column, table_range
    Shade_Cells table_range
    Shade_Cells mycell
End If
End Sub

Note: Shade_Cells and Set_Range_States are custom built.
Office ProductivityMicrosoft Excel

Avatar of undefined
Last Comment

8/22/2022 - Mon