Help with creating a board grid with VB

[UMAKEMESIK]

I have a sheet that calculates how many boxes in one direction and how many boxes in the other direction.
for example , I place the data in early columns and the sheet calculates I can use 3 boxes in one direction (D3) and three boxes in another shown at (D5)

I would like to show a grid to the right buy using dark boarders or filled in cells with color.

if the answer is 3 x 3 then VB would create a filled in grid of 3 cells to the right and 3 cells down. They would be filled with a generic color or a specified color and
each cell will have a dark thick boarder.

trying to create a graphic representation of 3 x 3 or whatever the numbers calculate out to be.

any help would be much appreciated.

 

[Peter_SSs]

See if something like this is any use.

Sub Make_Board()
  Dim Rws As Long, Cols As Long
  
  Const maxSize As Long = 50 '<- tThis would be max. no. or rows and max. no. of columns
  Const TopLeftCell As String = "F3"  '<-Top left cell of the board
  
  Rws = Range("D3").Value
  Cols = Range("D5").Value
  Application.ScreenUpdating = False
  With Range(TopLeftCell).Resize(maxSize, maxSize)
    .Interior.Color = xlNone
    .Borders.LineStyle = xlNone
    If Rws * Cols > 0 Then
      With .Resize(Rws, Cols)
        .Interior.Color = RGB(200, 200, 200)
        .BorderAround LineStyle:=xlContinuous, Weight:=xlThick
        With .Borders(xlInsideVertical)
          .LineStyle = xlContinuous
          .Weight = xlThick
        End With
        With .Borders(xlInsideHorizontal)
          .LineStyle = xlContinuous
          .Weight = xlThick
        End With
      End With
    End If
  End With
  Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

Here is another macro that you can consider...

Sub MakeBoard()
  Application.ReplaceFormat.Borders.Weight = 3
  With Range("F3").Resize([D3], [D5])
    .Replace "", "", , , , ,False , True
    .Interior.Color = vbYellow
  End With
End Sub

 

[UMAKEMESIK]

See if something like this is any use.

Sub Make_Board()
  Dim Rws As Long, Cols As Long
 
  Const maxSize As Long = 50 '<- tThis would be max. no. or rows and max. no. of columns
  Const TopLeftCell As String = "F3"  '<-Top left cell of the board
 
  Rws = Range("D3").Value
  Cols = Range("D5").Value
  Application.ScreenUpdating = False
  With Range(TopLeftCell).Resize(maxSize, maxSize)
    .Interior.Color = xlNone
    .Borders.LineStyle = xlNone
    If Rws * Cols > 0 Then
      With .Resize(Rws, Cols)
        .Interior.Color = RGB(200, 200, 200)
        .BorderAround LineStyle:=xlContinuous, Weight:=xlThick
        With .Borders(xlInsideVertical)
          .LineStyle = xlContinuous
          .Weight = xlThick
        End With
        With .Borders(xlInsideHorizontal)
          .LineStyle = xlContinuous
          .Weight = xlThick
        End With
      End With
    End If
  End With
  Application.ScreenUpdating = True
End Sub

View attachment 83759

Thanks so much for taking the time. I was able to get this work. when d3 and d5 are updated i have to press f5 to run a macro to update the graphic grid. I was thinking of placing a button on the page so when the numbers are updated i would click on the Run-macro button i place above the grid. Is there a way to have the grid auto update when the numbers change? Thanks in advance

 

[Peter_SSs]

Is there a way to have the grid auto update when the numbers change?

Yes.
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("D3, D5")) Is Nothing Then Make_Board
End Sub

 

[UMAKEMESIK]

Thanks again!!! Works as expected.

This new code works when an number is entered in D3/D5 and the ENTER KEY is pressed. As soon as the key is pressed the graphic changes.

On my sheet the cells D3/D5 are not changed directly. They are a result of a quick formula used to calculate those numbers resulted in D3/D5

My in put cells are A3/A5 - C3/C5

I use in D3:
=ROUNDDOWN(SUM(C3/A3),0)
and in D5:
=ROUNDDOWN(SUM(C5/A5),0)

So I dont use the enter key for the numbers to change.

is there a way for VB to replicate the use of the Enter Key so the graphic will change?

If not, can I place a button on the page to act as an Update button that will do the same as an enter key.


thanks again.

 

[Peter_SSs]

Change the previous code to this.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("A3, C3, A5, D5")) Is Nothing Then Make_Board
End Sub

BTW, 'SUM' in your formulas does nothing so can be left out. As well as that, I would make this change to the formulas so the board-making code does not error if the user happens to clear A3 or A5 or some other error occurs with the formula

=IFERROR(ROUNDDOWN(C3/A3,0),0)

=IFERROR(ROUNDDOWN(C5/A5,0),0)